﻿unit PushConDB.Module;
 {$I PushConAppSrvr.inc}
interface

uses
  System.SysUtils, System.Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Error, FireDAC.Stan.Param, FireDAC.DatS, FireDAC.Phys.Intf,
  FireDAC.DApt.Intf, FireDAC.Stan.Async, FireDAC.Comp.Client, FireDAC.UI.Intf,
  FireDAC.Stan.Def, FireDAC.Stan.Pool, FireDAC.Phys, FireDAC.Phys.MSSQL,
  FireDAC.Phys.MSSQLDef, FireDAC.VCLUI.Wait, FireDAC.DApt, Datasnap.Provider,
  Data.DB, FireDAC.Comp.DataSet,System.IniFiles,
  DBXJSON, DBXJSONReflect,System.JSON,VsUtils,System.DateUtils;

type
  TPSConDataModule = class(TDataModule)
    PSCon_Connection: TFDConnection;
    PSCon_Query: TFDQuery;
    PSCon_DataSetProvider: TDataSetProvider;
    PSCon_Command: TFDCommand;
    Total_Query: TFDQuery;
    FDQuery1: TFDQuery;
    procedure DataModuleDestroy(Sender: TObject);
    procedure PSCon_ConnectionBeforeConnect(Sender: TObject);
  private
    { Private declarations }
     procedure posCreateGroup(GCKH_ID,groupname,maxusers, owner:string);
     function GetSCRWBH(ID:String;aDate: TDateTime):string;
     function AutoCode(aCode: string): string;
     function CreateNumStr(Num: int64; Len: integer): string;
     procedure addGroupMember(group_id, username: string);
     function EaseCreateGroup(groupname,desc,maxusers,owner,members:string):String;
     procedure CreateAppGroup(GCMC,owner,GCKH_ID:string);

     function GetSql(FiledName,Wr,WrRQ:string):string;
     function GetToTalSql(FiledName,Wr,WrRQ:string):string;
     procedure UpdateGKJLSum(Sqlstr:string);
     procedure UpdateToTalGKJLSum(Sqlstr:string);
     procedure UpdateGKJL(Sqlstr:string);
     function GetGKJLSql(FiledName,Wr:string):string;

  public
    { Public declarations }
    function DoQuery(Sql:string):String;
    function Fatongtaizhang(Search,FromTM, ToTM,GCKH_MC:string;
      PageNo:Integer; ISGC,JZ:Integer; UserFID:string):String;
    function FatongtaizhangZ(Search,FromTM, ToTM,GCKH_MC:string;
      PageNo,ISGC:Integer;UserFID:string;IsSH:Integer=0):String;

    function getGCKHMC(GCKH_ID:string):string;

    function GetCpGongChengKH(Search,FromTM, ToTM:string;PageNo:Integer):String;
    function GetGongCKH(Search:string; PageNo:Integer):String;
    function CreateGroup(PageNo:Integer):String;
    function GetConFenZiGS(Search:string;PageNo:Integer):String;

    function FaTYS(FromTM, ToTM,SCRW_ID:string):String;
    function cpFaTongYS(FromTM, ToTM,SCRW_ID:string;PageNo:Integer=1):String;
    function Gongkongsc(Search,FromTM,ToTM,UserFID,GCKH_MC,SCMS:string;
         PageNo,JZ:Integer):String;
     function GongkongscZ(Search,FromTM,ToTM,UserFID,GCKH_MC:string;
         PageNo,JZ:Integer):String;
    function ciGongKongJL(GKJL_FID, SCRQ:string;JZ:Integer):String;
    function SelectRBMX(FromTM,ToTM,GCKH_ID:string):String;
    function Shengchanrb(Search, FromTM, ToTM,UserFID:string;ISGC,PageNo:Integer):String;
    function SelectSCRW(FromTM,ToTM, GCKH_ID,TBH:string;PageNo,ISSH:Integer):String;
    function cpShengChanRW_CX(Search,FromTM, ToTM:string;PageNo:Integer):String;
    function GetGuiGBH(Search:string;PageNo:Integer):String;
    function GetFuJiaBH(Search:string;PageNo:Integer):String;
    function ShengChanRenWu(Search, FromTM, ToTM,UserFID,GCKH_MC:string;
             PageNo,RWZT,ISGC:Integer):String;
    function ShengChanRenWu_TZ(FromTM,ToTM,Search, SCRW_ID:string;JZ,ISSH,PageNo:Integer):String;
    function InsertSCRW(GCKH_ID,GGBH_ID,JHSJ,JZBW,ISBJ,SJSL,TLD,GGBH,FJBH,JHSL,
           SCRW_LXR,SCRW_LXRFS:string;GGBH_LX,IsBS:Integer):String;

    function YuanLiaoYSTZ(Search, FromTM, ToTM,UserFID,YLYS_YLPZ,YLYS_GGBH:string;
          PageNo,YLPZ_LX:Integer):String;
    function YuanLiaoYSRB(Search, FromTM, ToTM,UserFID:string;PageNo:Integer):String;
    function GetDetail(YLPZ_ID, FromTM, ToTM,UserFID:string):String;
    function GetFZGSMC(UserFID:string;ISGC:Boolean = False):string;
    function SetCreateGroup:string;
    function getUseradmin(GCKH_ID:string):Variant;

    function GetSumFaTongTJ(BegDate,GetDate,FZGS_ID,Search,GCKH_MC:string;ISGC,IsSH,PageNo:Integer):string;
    function GetSumFatongtaizhang(BegDate,GetDate,FZGS_ID,Search,GCKH_MC:string;ISGC,IsSH,PageNo:Integer):string;
    function GetSumFaTongTJMX(BegDate,GetDate,FZGS_ID,Search,GCKH_MC:string;ISGC,IsSH:Integer):string;

    function GetSumCmYuanLiaoYS(BegDate,GetDate,FZGS_ID,Search,YLPZ_MC:string;YLPZ_LX,PageNo:Integer):string;
    function GetSumCmYuanLiaoYSMX(BegDate,GetDate,FZGS_ID,Search,YLPZ_MC:string;YLPZ_LX:Integer):TFDQuery;
    function GetSumFaTongTJMX2(FormTm,ToTM,GCKH_ID,FZGS_ID:string;PageNo:Integer=0):string;
    function GetSumGongKongJL(FormTm, ToTM,FZGS_ID,Search,SCMS,GCKH_MC:String):string;
    Function GetSumGongKongJLMX(FormTm,ToTM,FZGS_ID,Search,SCMS,GCKH_MC:string):string;

    function GetcmKuCunLW(DT,UserFID:string):TFDQuery;
    function cmLiaoWeiBG(KCLW_ID,LWBG_DT:string):string;
    function QuerySCRW_SCPB(RWZT,PBZT, ND, YF, KW: integer;KeyWd,UserFID: string;
      FromDt,TromDt,RWBH,GGBH,PBBH:string;PZLX,PageNo:Integer):string;
    function GetCpshengChanPB(SCRW_ID,UserFID:string):TFDQuery;
    function GetGCKH_MC(FromTM, ToTM, GCKH_MC :string):string;
    function GetSumShengCXH(FormTm,ToTM,FZGS_ID,Search,SCMS,GCKH_MC:string):string;
    function GetCompany(ID:string):Variant;
    function Daily_Monthly_Annual(Daily,Monthly,SCMS,FZGS_ID:string):String;

    function Get_Daily_Monthly_Annual(FromTM, ToTM,SCMS,FZGS_ID:string;IsD,IsM,IsA:Boolean):String;
    function Get_YLPZ_MC(YLPZ_LX:Integer):string;
  end;

  const
    Top =10;
var
  PSConDataModule: TPSConDataModule;
type
  TStrArr = array of string;   // 字符串动态数组
implementation

 uses
   ConfingU,Ecaan.DB,PushConApp.command,
   Ecaan.Command,MVCFramework.RESTClient,MVCFramework.Logger;
{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}

{ TDataModule4 }

procedure TPSConDataModule.addGroupMember(group_id, username: string);
  var
  JSONObject:TJSONObject;
  LBody: string;
  LResp: IRESTResponse;
  FRESTClient:TRESTClient;
begin
 try
   JSONObject := TJSONObject.Create(nil);
   {$IFDEF SSL}  //SSL Web
     FRESTClient := TRESTClient.Create('https://www.pushcon.ltd',443);
   {$ELSE}
     TRESTClient.Create('http://127.0.0.1',TConfigFile.GetAPIPort);
   {$ENDIF}
  with FRESTClient do
  begin
     LBody :=Format('group_id=%s&username=%s',
      [group_id,username]);
    LResp := doPOST('/Easemob/addGroupMember',[],LBody);
  end;
 finally
   FRESTClient.Free;
 end;

end;

function TPSConDataModule.AutoCode(aCode: string): string;
var
  sl, sr: string;
  i, j: integer;
begin
  j := -1;

  for i := 1 to Length(aCode) do
  begin
    sl := copy(aCode, 1, Length(aCode) - i);
    sr := copy(aCode, Length(aCode) - i + 1, i);

//    if not StrIsDigit(Sr) then  // 非字符
//    begin
//      j := i - 1;
//
//      // 重新取上一个
//      sl := copy(aCode, 1, Length(aCode) - j);
//      sr := copy(aCode, Length(aCode) - j + 1, j);
//
//      break;  // 跳出，
//    end;
  end;

  if j = -1 then
    j := i - 1;

  if j = 0 then
    j := Length(aCode);

  // 加1返回
  Result := sl + CreateNumStr(StrToInt64Def(sr, 0) + 1, j);
end;

function TPSConDataModule.ciGongKongJL(GKJL_FID, SCRQ:string;JZ:Integer):String;
var
  Wr,SCWr,WrRQ, Sqlstr, Ord: string;
  FiledName:string;
  I:Integer;
begin
 try
  Wr :=Format('GKJL_FID=''%s'' and GKJL_生产日期=''%s'' and UNIT=%d',
    [GKJL_FID, SCRQ, JZ]);

  Total_Query.SQL.Clear;
  Total_Query.SQL.Text :=CombinQuerySql(
   'GKJL_PNO,GKJL_生产时间 GKJL_MTM,GKJL_生产盘量 GKJL_PL,UNIT',
   'ciGongKongJL with(nolock)',Wr);
  Total_Query.Open();
  // GKJL_PNO,GKJL_MTM,GKJL_PL,UNIT


   Sqlstr :=  Format('if exists (%s %s', [
      'select * from tempdb.dbo.sysobjects '+
      'where id = object_id(N''tempdb..#ToTalGKJL'') and type=''U'') drop table #ToTalGKJL',
       'create table #ToTalGKJL '+   // 创建临时表#Tmp
        '( '+
        ' GKJL_PNO int,'+
        ' GKJL_MTM datetime ,'+
        ' GKJL_PL numeric(8,3),'+
        ' UNIT int,'+
        ' YLMC   varchar(100) not null,'+//创建列ID,并且每次新增一条记录就会加1
        ' 设定值  numeric(20,4), '+
        ' 计量值   numeric(20,4), ' +
        ' 误差值   numeric(20,4), ' +
        ' 误差率   numeric(20,4)' +
        ')'
      ]);
  PSCon_Connection.ExecSQL(Sqlstr);

  for I := 1 to 22 do
    begin
      FiledName:='SCPB_水简称';
      case I of
        1:begin
         FiledName:='SCPB_水简称 AS YLMC ,GKJL_水_设定  AS 设定值 ,GKJL_水_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;

        2:begin
         FiledName:='SCPB_水2简称 AS YLMC ,GKJL_水2_设定  AS 设定值 ,GKJL_水2_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        3:begin
         FiledName:='SCPB_胶料简称 AS YLMC ,GKJL_胶料_设定  AS 设定值 ,GKJL_胶料_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        4:begin
         FiledName:='SCPB_胶料2简称 AS YLMC ,GKJL_胶料2_设定  AS 设定值 ,GKJL_胶料2_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        5:begin
         FiledName:='SCPB_胶料3简称 AS YLMC ,GKJL_胶料3_设定  AS 设定值 ,GKJL_胶料3_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        6:begin
         FiledName:='SCPB_掺料简称 AS YLMC ,GKJL_掺料_设定  AS 设定值 ,GKJL_掺料_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        7:begin
         FiledName:='SCPB_掺料2简称 AS YLMC ,GKJL_掺料2_设定  AS 设定值 ,GKJL_掺料2_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        8:begin
         FiledName:='SCPB_掺料3简称 AS YLMC ,GKJL_掺料3_设定  AS 设定值 ,GKJL_掺料3_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        9:begin
         FiledName:='SCPB_掺料4简称 AS YLMC ,GKJL_掺料4_设定  AS 设定值 ,GKJL_掺料4_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        10:begin
         FiledName:='SCPB_细骨简称 AS YLMC ,GKJL_细骨_设定  AS 设定值 ,GKJL_细骨_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        11:begin
         FiledName:='SCPB_细骨2简称 AS YLMC ,GKJL_细骨2_设定  AS 设定值 ,GKJL_细骨2_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        12:begin
         FiledName:='SCPB_细骨3简称 AS YLMC ,GKJL_细骨3_设定  AS 设定值 ,GKJL_细骨3_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        13:begin
         FiledName:='SCPB_粗骨简称 AS YLMC ,GKJL_粗骨_设定  AS 设定值 ,GKJL_粗骨_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        14:begin
         FiledName:='SCPB_粗骨2简称 AS YLMC ,GKJL_粗骨2_设定  AS 设定值 ,GKJL_粗骨2_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        15:begin
         FiledName:='SCPB_粗骨3简称 AS YLMC ,GKJL_粗骨3_设定  AS 设定值 ,GKJL_粗骨3_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        16:begin
         FiledName:='SCPB_外剂简称 AS YLMC ,GKJL_外剂_设定  AS 设定值 ,GKJL_外剂_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        17:begin
         FiledName:='SCPB_外剂2简称 AS YLMC ,GKJL_外剂2_设定  AS 设定值 ,GKJL_外剂2_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        18:begin
         FiledName:='SCPB_外剂3简称 AS YLMC ,GKJL_外剂3_设定  AS 设定值 ,GKJL_外剂3_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        19:begin
         FiledName:='SCPB_外剂4简称 AS YLMC ,GKJL_外剂4_设定  AS 设定值 ,GKJL_外剂4_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        20:begin
         FiledName:='SCPB_外剂5简称 AS YLMC ,GKJL_外剂5_设定  AS 设定值 ,GKJL_外剂5_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        21:begin
         FiledName:='SCPB_其他简称 AS YLMC ,GKJL_其他_设定  AS 设定值 ,GKJL_其他_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
        22:begin
         FiledName:='SCPB_其他2简称 AS YLMC ,GKJL_其他2_设定  AS 设定值 ,GKJL_其他2_计量 as 计量值';
         Sqlstr :=GetGKJLSql(FiledName,Wr);
         UpdateGKJL(Sqlstr);
        end;
      end;
    end;

   Sqlstr :=CombinQuerySql('*','#ToTalGKJL','',
    'Order By GKJL_PNO,GKJL_MTM,GKJL_PL,UNIT,YLMC');
   PSCon_Query.Close;
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Add('execute PRO_CiGongKSC :Sql');
   PSCon_Query.ParamByName('Sql').Value :=Sqlstr;
   PSCon_Query.Active :=True;

   Result := GetAppDataToJson( PSCon_Query.RecordCount,
     AppDataToJson(Total_Query,Total_Query.RecordCount),
     AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));

 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.cmLiaoWeiBG(KCLW_ID,
  LWBG_DT: string): string;
  Var
   Wr:string;
begin
  Wr := Format('KCLW_ID=''%s''',[KCLW_ID]);

  if LWBG_DT<>'' then
    Wr := CombinAndSql(Wr,Format('convert(char(10), LWBG_DT, 20)=''%s''', [LWBG_DT]))
   else
    Wr := CombinAndSql(Wr,Format('convert(char(10), LWBG_DT, 20)=''%s''', [DateToStr(Date)])) ;
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text :=  CombinQuerySql('*','cmLiaoWeiBG with(nolock)',Wr,'Order by LWBG_DT');
   PSCon_Query.Open();

   Result := GetAppDataToJson(PSCon_Query.RecordCount,
     AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
end;

function TPSConDataModule.cpFaTongYS(FromTM, ToTM, SCRW_ID: string;
  PageNo: Integer):String;
var
  Wr: string;
begin
 Try
  if (FromTM = ToTM) then
    Wr := Format('convert(char(10), FTYS_时间, 20)=''%s''', [FromTM])
  else
    Wr := Format('FTYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]);

    Wr :=CombinAndSql(Wr,Format('SCRW_ID=''%s''',[SCRW_ID]));

   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := Format('select top '+ intToStr(Top) +'* '+
   'from (select row_number()over(order by FTYS_时间)rownumber,'+
   'FTYS_编号 FTYS_BH,RIGHT(CONVERT(varchar(20), [FTYS_时间], 20),14) FTYS_MTM,'+
   'FTYS_累计车次 FTYS_LJCC,FTYS_车辆编号_ FTYS_CLBH,FTYS_车辆牌号_ FTYS_CPH,'+
   'isnull(FTYS_运输数量,0) FTYS_YSSL ,isnull(FTYS_生产数量,0) FTYS_SCSL'+
   '  from cpFaTongYS with(nolock)  where %s) FTYS '+
   format('where rownumber> %d',[Top*(PageNo - 1)]),[Wr]);
   PSCon_Query.Open;

  Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
    'cpFaTongYS with(nolock) ',Wr),AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.cpShengChanRW_CX(Search, FromTM, ToTM: string;
  PageNo: Integer):String;
  var
   Wr:string;
begin
   if FromTM = ToTM then
    ToTM :=DateTimeToStr(GetStrTodateTime(ToTM)+1/24);
   if Search <>'' then
    Search :=Format(' and SCRW_编号 like ''%%%s%%'' or SCRW_部位名称 like ''%%%s%%'''+
    'or SCRW_规格标号_+SCRW_附加标号 like ''%%%s%%''',[Search,Search,Search])
   else
     Search :='';
   Wr := CombinAndSql(Wr,Format('FTYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]));

 Try
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := Format('select top '+ intToStr(Top) +'* '+
   'from (select row_number()over(order by SCRW_编号)rownumber,'+
    'SCRW.SCRW_ID,SCRW_编号 SCRW_BH,SCRW_输送方式 SCRW_JZFS,SCRW_部位名称 SCRW_BWMC,'+
    'SCRW_规格标号_+SCRW_附加标号 SCRW_TBH,sum(isnull(SCRW_计划数量,0)) SCRW_JHSL,'+
    'max(FTYS_累计车次) FTYS_LJCC,SUM(isnull(FTYS_生产数量,0))FTYS_SCSL,'+
    'SUM(isnull(FTYS_运输数量,0))FTYS_YSSL'+
   ' from cpShengChanRW SCRW left join cpFaTongYS FTYS on  SCRW.SCRW_ID=FTYS.SCRW_ID'+
   ' where %s group by SCRW.SCRW_ID,SCRW_编号,SCRW_输送方式,SCRW_部位名称'+
   ',SCRW_规格标号_+SCRW_附加标号'+
   '  ) rwcx_ '+
   ' where  rownumber>'+IntToStr(Top*(PageNo - 1)),[Wr]);
   PSCon_Query.Active := True;


 Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
   '('+
    CombinQuerySql('count(*) rows',
     'cpShengChanRW SCRW with(nolock)'+
    ' left join  cpFaTongYS FTYS on  SCRW.SCRW_ID=FTYS.SCRW_ID',Wr,
    'group by SCRW.SCRW_ID,SCRW_编号,SCRW_输送方式,SCRW_部位名称,'+
    'SCRW_规格标号_+SCRW_附加标号')+
    ')A' ),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount))
  except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

procedure TPSConDataModule.CreateAppGroup(GCMC,owner,GCKH_ID: string);
  var
   Json:TJSONObject;
   i,L:Integer;
   HX_ID,NSql :string;
   VarArr: TStrArr;
begin
     if owner.Length =0 then
     begin
       LogI('! 创建群管理员不存在');
       Exit;
     end;

      JSON :=TJSONObject.ParseJSONValue(
       EaseCreateGroup(GCMC,GCMC,'150',owner,''),True) as TJSONObject;
     if JSON.Count>0 then
      begin
       HX_ID :=JSON.GetValue('groupid').Value;
       NSql := Format('if not exists (%s) %s else %s',[
       CombinExistSql(format('%s..createGroup',[TConfigFile.GetAppDb]),
       Format('G_ID=''%s''',[GCKH_ID])),
       CombinInsertSql(format('%s..createGroup',[TConfigFile.GetAppDb]),'G_ID,HX_ID,groupname,[owner],maxusers',
       Format(' ''%s'',''%s'',''%s'',''%s'',%d',[
          GCKH_ID,HX_ID,GCMC,owner,150])),
       CombinUpdateSql(format('%s..createGroup',[TConfigFile.GetAppDb]),
       Format('HX_ID=''%s'',groupname=''%s'',[owner]=''%s'',maxusers=%d',[
        HX_ID,GCMC,owner,150]),
       Format('G_ID=''%s''',[GCKH_ID]))
       ]);

       with PSCon_Command do
       begin
         CommandText.Text :=NSql;
         Execute();
       end;
       LogI('@ 创建群 '+HX_ID+' 成功');

       if HX_ID.Length>0 then
        begin
          addGroupMember(HX_ID,owner)
        end;
      end
      else begin
       LogI('!'+GCMC+' 创建群失败,owner 用户不存在');
       Exit;
      end;
end;

function TPSConDataModule.CreateGroup(PageNo: Integer):String;
var
  GCKH_ID,WX_ID:string;
  I: Integer;
begin
 Try
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text :=CombinQuerySql('top '+intToStr(Top)+' *',
   '('+CombinQuerySql('row_number()over(order by GCKH_ID) rownumber,'+
   'GCKH_ID,GCKH_工程名称 GCKH_MC','cpGongChengKH with(nolock)',
    format('isnull(GCKH_工程状态,0)=0 and GCKH_ID<>''%s''',
      ['{00000000-0000-0000-0000-000000000000}']))+')GCKH ',
   format('rownumber> %d',[Top*(PageNo - 1)]));
   PSCon_Query.Open();

  PSCon_Query.First;
  while not PSCon_Query.Eof do
  begin
    GCKH_ID :=PSCon_Query.FieldByName('GCKH_ID').AsString;
     posCreateGroup(GCKH_ID,PSCon_Query.FieldByName('GCKH_MC').AsString,
       '150','');
    PSCon_Query.Next;
  end;

  Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
    'cpGongChengKH  with(nolock)',format('isnull(GCKH_工程状态,0)=0 and GCKH_ID<>''%s''',
      ['{00000000-0000-0000-0000-000000000000}'])),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount))
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.CreateNumStr(Num: int64; Len: integer): string;
begin
  Result := Format('%.' + IntToStr(Len) + 'd',[Num]);
end;

function TPSConDataModule.Daily_Monthly_Annual(Daily, Monthly,SCMS,FZGS_ID: string): String;
 var
   Sqlstr,Daily_FromTM,Daily_ToTM,Monthly_FromTM,Monthly_ToTM,
   Annual_FromTM,Annual_ToTM,Annual,Wr,DWr,MWr,AWr:string;
   Daily_YSSL,Monthly_YSSL,Annual_YSSL:Double;
   Daily_车次,Monthly_车次,Annual_车次:Integer;
begin
    Sqlstr :=  Format('if exists (%s %s', [
      'select * from tempdb.dbo.sysobjects '+
      'where id = object_id(N''tempdb..#Daily_Monthly_Annual'') and type=''U'')'+
      ' drop table #Daily_Monthly_Annual',
      ' create table #Daily_Monthly_Annual '+// 创建临时表#Tmp
        '( '+
        ' Daily_FromTM datetime,'+
        ' Daily_ToTM datetime,'+
        ' Daily_YSSL  numeric(20,4),'+
        ' Daily_车次 int,'+
        ' Monthly_FromTM datetime,'+
        ' Monthly_ToTM datetime,'+
        ' Monthly_YSSL  numeric(20,4),'+
        ' Monthly_车次 int,'+
        ' Annual_FromTM datetime,'+
        ' Annual_ToTM datetime,'+
        ' Annual_YSSL  numeric(20,4),'+
        ' Annual_车次 int'+
        ')'
      ]);
    PSCon_Connection.ExecSQL(Sqlstr);
   Daily_FromTM :=FormatDateTime('YYYY-MM-DD',Date)+' '+Daily;
   Daily_ToTM :=DateTimeToStr(GetStrTodateTime(Daily_FromTM)+1);

   if (Now<GetStrTodateTime(Daily_FromTM)) then
   begin
     Daily_FromTM := DateTimeToStr(GetStrTodateTime(Daily_FromTM)-1);
     Daily_ToTM :=DateTimeToStr(GetStrTodateTime(Daily_FromTM)+1);
   end;
   if SCMS<>'' then
      Wr :=CombinAndSql(Wr,Format('生产模式=''%s''',[SCMS]));

   if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
      Wr := CombinAndSql(Wr,Format('GKSC_FZGS=''%s''',[FZGS_ID]));
  try
    DWr :=CombinAndSql(Wr,Format('SCTM between ''%s'' and ''%s'' ',
      [Daily_FromTM,Daily_ToTM]));
    PSCon_Query.Open(CombinQuerySql('sum(isnull(生产数量,0)) as GKJL_YSSL,Count(*) 累计车次',
      'CiGongKongSC_intf with(Nolock)',DWr ));

   Daily_YSSL := PSCon_Query.FieldByName('GKJL_YSSL').AsFloat;
   Daily_车次 := PSCon_Query.FieldByName('累计车次').AsInteger;
  except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
  end;

   Annual :=FormatDateTime('YYYY',Date);

   Monthly_FromTM :=Annual+'-'+FormatDateTime('MM',IncMonth(Date,-1))+'-'+Monthly+' '+Daily;

   Monthly_ToTM :=DateTimeToStr(GetStrTodateTime(Annual+'-'+
       FormatDateTime('MM',Date)+'-'+Monthly+' '+ Daily));

   MWr :=CombinAndSql(Wr,Format('SCTM between ''%s'' and ''%s''',
      [Monthly_FromTM,Monthly_ToTM]));

  PSCon_Query.Open(CombinQuerySql('sum(isnull(生产数量,0)) as GKJL_YSSL,Count(*) 累计车次',
      'CiGongKongSC_intf with(Nolock)',MWr));

   Monthly_YSSL := PSCon_Query.FieldByName('GKJL_YSSL').AsFloat;
   Monthly_车次 := PSCon_Query.FieldByName('累计车次').AsInteger;

  Annual_FromTM :=FormatDateTime('YYYY',IncYear(Date,-1))+'-12'+'-'+Monthly+' '+ Daily;
  Annual_ToTM :=Annual+'-12'+'-'+Monthly+' '+ Daily;

  AWr :=CombinAndSql(Wr,Format('SCTM between ''%s'' and ''%s''',
      [Annual_FromTM,Annual_ToTM]));
 PSCon_Query.Open(CombinQuerySql('sum(isnull(生产数量,0)) as GKJL_YSSL,Count(*) 累计车次',
      'CiGongKongSC_intf with(Nolock)',AWr));
   Annual_YSSL := PSCon_Query.FieldByName('GKJL_YSSL').AsFloat;
   Annual_车次 := PSCon_Query.FieldByName('累计车次').AsInteger;

  with PSCon_Command do
  begin
    CommandText.Text := CombinInsertSql('#Daily_Monthly_Annual',
        'Daily_FromTM,Daily_ToTM,Daily_YSSL,Daily_车次,'+
        'Monthly_FromTM,Monthly_ToTM,Monthly_YSSL,Monthly_车次,'+
        'Annual_FromTM,Annual_ToTM,Annual_YSSL,Annual_车次',
        Format(' ''%s'',''%s'',%.4f,%d,''%s'',''%s'',%.4f,%d,''%s'',''%s'',%.4f,%d',
        [Daily_FromTM,Daily_ToTM,Daily_YSSL,Daily_车次,
         Monthly_FromTM,Monthly_ToTM,Monthly_YSSL,Monthly_车次,
         Annual_FromTM,Annual_ToTM,Annual_YSSL,Annual_车次 ]));
    Execute();
  end;

  PSCon_Query.Open(CombinQuerySql('*','#Daily_Monthly_Annual'));
  Result := GetAppDataToJson(PSCon_Query.RecordCount,
     AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
end;

procedure TPSConDataModule.DataModuleDestroy(Sender: TObject);
begin
    if Assigned(PSCon_Connection) then
    FreeAndNil(PSCon_Connection);
end;

function TPSConDataModule.DoQuery(Sql: string): String;
  var
  JsonStr: string;
begin
 try
   Result := '';
    if not PSCon_Connection.Connected then
    PSCon_Connection.Open;
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := Sql;
   PSCon_Query.Active := True;

  JsonStr :=DataSetToJson(PSCon_Query,PSCon_Query.RecordCount);
  Result := JsonStr;
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.EaseCreateGroup(groupname, desc, maxusers, owner,
  members: string):String;
  var
  JSONObject:TJSONObject;
  LBody,RommID,error,Msg: string;
  LResp: IRESTResponse;
  FRESTClient:TRESTClient;
  Code:Integer;
begin
 try
   JSONObject := TJSONObject.Create(nil);
   {$IFDEF SSL}  //SSL Web
     FRESTClient := TRESTClient.Create('https://www.pushcon.ltd',443);
   {$ELSE}
     TRESTClient.Create('http://127.0.0.1',TConfigFile.GetAPIPort);
   {$ENDIF}
  with FRESTClient do
  begin
     LBody :=Format('groupname=%s&desc=%s&maxusers=%s&owner=%s&members=[%s]',
      [groupname,desc,maxusers,owner,members]);
    LResp := doPOST('/Easemob/createGroup',[],LBody);
  end;
  if not LResp.HasError then
    begin
      JSONObject := TJSONObject.ParseJSONValue(LResp.BodyAsString) as TJSONObject;
      Result :=JSONObject.ToJSON;
    end else
    Result :=JSONObject.ToJSON;
 finally
   JSONObject.Free;
   FRESTClient.Free;
 end;

end;
function TPSConDataModule.Fatongtaizhang(Search, FromTM, ToTM,GCKH_MC: string;
  PageNo: Integer; ISGC,JZ: Integer; UserFID: string):String;
var
  Wr,RQWR: string;
begin
 Try
  if (FromTM = ToTM) then
    RQWR := Format('convert(char(10), FTYS_时间, 20)=''%s''', [FromTM])
  else
    RQWR := Format('FTYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]);
   Wr :=RQWR;
  if (ISGC = 1) then
			Wr := CombinAndSql (Wr, Format('GCKH.GCKH_ID= ''%s''',[UserFID] ))
		 else
      begin
			if (UserFID <> '{11111111-1111-1111-1111-111111111111}') then
				Wr := CombinAndSql (Wr, format('FTYS_FZGS= ''%s''',[UserFID]));
      end;

		if (Search <> '') then
     begin
			Search := Format('(FTYS_编号 like ''%%%s%%''  or FTYS_车辆编号 like ''%%%s%%'''+
      ' or FTYS_车辆牌号 like ''%%%s%%'' or GCKH_工程名称 like ''%%%s%%'''+
      ' or SCRW.SCRW_规格标号_+SCRW.SCRW_附加标号 like ''%%%s%%'')',
      [Search,Search,Search,Search,Search]);
			Wr := CombinAndSql ( Wr, Search);
     end;

     if GCKH_MC<>'' then
       Wr := CombinAndSql ( Wr, Format('GCKH_工程名称 like ''%%%s%%''',[GCKH_MC]));
     if JZ>0 then
       Wr := CombinAndSql ( Wr, Format('FTYS_生产机组 = %d',[JZ]));

     if not PSCon_Connection.Connected then
      PSCon_Connection.Open;

   Total_Query.SQL.Clear;
   Total_Query.SQL.Text :=  CombinQuerySql(
     'sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,'+
     'sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,Count(*) 累计车次',
     'cpfatongysView FTYS with(Nolock) '+
     'left join cpGongChengKH GCKH with(Nolock) on FTYS.GCKH_ID=GCKH.GCKH_ID '+
     'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',
     'FTYS_签发审核=1 and '+Wr);
   Total_Query.Open;

   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := Format('select top '+ intToStr(Top) +
      '* from (select row_number()over(order by FTYS_时间)rownumber,'+
			'FTYS.GCKH_ID,GCKH.GCKH_工程名称 GCMC,GCKH.GCKH_施工单位 SGDW,'+
			'FTYS_FZGS,FTYS_编号 FTYS_BH,FTYS_时间 FTYS_MTM,FTYS_运输内容名称 FTYS_TYPE,'+
			'SCRW_规格标号_+SCRW.SCRW_附加标号 HNT_BH,FTYS_车辆编号 FTYS_CLBH,'+
      'FTYS_车辆牌号 FTYS_CPH,FTYS_商砼数量2 FTYS_YSSL,FTYS_累计数量 FTYS_LJSL,'+
      'FTYS_累计车次 FTYS_LJCC '+
      'from cpfatongysView FTYS with(Nolock) '+
      'left join cpGongChengKH GCKH with(Nolock) on FTYS.GCKH_ID=GCKH.GCKH_ID '+
      'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID where %s) FTYS'+
      ' where  rownumber>'+IntToStr(Top*(PageNo - 1)),[Wr]);;
   PSCon_Query.Active := True;

   Result := GetAppDataToJson(DB.QueryValue('count(*) rows',
    'cpfatongysView FTYS '+
    'left join cpGongChengKH GCKH on FTYS.GCKH_ID=GCKH.GCKH_ID '+
    'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',Wr),
    AppDataToJson(Total_Query,Total_Query.RecordCount),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;


function TPSConDataModule.FatongtaizhangZ(Search, FromTM, ToTM, GCKH_MC: string;
  PageNo,ISGC: Integer; UserFID: string;IsSH:Integer): String;
var
  Wr,SWr,RQWR,GCSearch,RWSearch: string;
begin
 Try
    Wr :='ISNULL(FTYS_DEL, 0) = 0';

      if (FromTM = ToTM) then
        Wr := CombinAndSql (Wr,Format('convert(char(10), FTYS_时间, 20)=''%s''', [FromTM]))
      else
        Wr := CombinAndSql (Wr,Format('FTYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]));

  if (ISGC = 1) then
			Wr := CombinAndSql (Wr, Format('GCKH.GCKH_ID= ''%s''',[UserFID] ))
		 else
      begin
			if (UserFID <> '{11111111-1111-1111-1111-111111111111}') then
				Wr := CombinAndSql (Wr, format('FTYS_FZGS= ''%s''',[UserFID]));
      end;

  if GCKH_MC<>'' then
       Wr := CombinAndSql ( Wr, Format('GCKH_工程名称 like ''%%%s%%''',[GCKH_MC]));

		if (Search <> '') then
     begin
      Wr := CombinAndSql (Wr, Format('FTYS_编号 like ''%%%s%%'''+
      ' or FTYS_车辆编号_ like ''%%%s%%'''+
      ' or FTYS_车辆牌号_ like ''%%%s%%'' or GCKH_工程名称 like ''%%%s%%'''+
      ' or SCRW_规格标号_+SCRW_附加标号 like ''%%%s%%''',
      [Search,Search,Search,Search,Search]));

      GCSearch :=Format('and (GCKH_工程名称 like ''%%%s%%'')',[Search]);
      RWSearch :=Format('or (SCRW_规格标号_+SCRW_附加标号 like ''%%%s%%'')',[Search]);
     end;

  if not PSCon_Connection.Connected then
    PSCon_Connection.Open;

   if IsSH>0 then
   begin
     Wr := CombinAndSql(Wr,'FTYS_回单审核>=2');
   end
   else
    begin
     Wr := CombinAndSql(Wr,'FTYS_签发审核=1');
    end;

    Total_Query.SQL.Clear;
     Total_Query.SQL.Text :=  CombinQuerySql(
     'sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,'+
     'sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,Count(*) 累计车次',
     'cpfatongys FTYS with(Nolock) '+
     'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID '+
     'left join cpGongChengKH GCKH with(Nolock) on SCRW.GCKH_ID=GCKH.GCKH_ID ',Wr);
     Total_Query.Open;

  PSCon_Query.SQL.Clear;
  PSCon_Query.SQL.Text :=CombinQuerySql('GCKH.GCKH_ID,GCKH_工程名称 GCKH_MC,GCKH_施工单位 SGDW,'+
     'sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,'+
     'Count(*) 累计车次',
     'cpFaTongYS FTYS with (Nolock) '+
     'Left join cpShengChanRW SCRW with (Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID '+
     'left join cpGongChengKH GCKH with(Nolock) on SCRW.GCKH_ID=GCKH.GCKH_ID',
      Wr,
     'Group By GCKH.GCKH_ID,GCKH_工程名称,GCKH_施工单位  order by GCKH_工程名称');
   PSCon_Query.Open();

   Result := GetAppDataToJson(DB.QueryValue('count(*) rows',
    'cpGongChengKH with(Nolock)',
    Format('GCKH_ID in (%s) %s',[CombinQuerySql('GCKH_ID','cpShengChanRW with(Nolock)',
    Format('SCRW_ID in (%s)%s ',[CombinQuerySql('SCRW_ID','cpfatongys with(Nolock)',
    Wr),RWSearch])),GCSearch])),

    AppDataToJson(Total_Query,Total_Query.RecordCount),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;

end;

function TPSConDataModule.FaTYS(FromTM, ToTM, SCRW_ID: string):String;
var
  Wr: string;
begin
 Try
  if (FromTM = ToTM) then
    Wr := Format('convert(char(10), FTYS_时间, 20)=''%s''', [FromTM])
  else
    Wr := Format('FTYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]);

    Wr :=CombinAndSql(Wr,Format('SCRW_ID=''%s''',[SCRW_ID]));

   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := Format('select top '+ intToStr(Top) +'* '+
   'from (select row_number()over(order by FTYS_时间)rownumber,'+
   'FTYS_编号 FTYS_BH,RIGHT(CONVERT(varchar(20), [FTYS_时间], 20),14) FTYS_MTM,'+
   'FTYS_累计车次 FTYS_LJCC,FTYS_车辆编号_ FTYS_CLBH,FTYS_车辆牌号_ FTYS_CPH,'+
   'isnull(FTYS_运输数量,0) FTYS_YSSL ,isnull(FTYS_生产数量,0) FTYS_SCSL'+
   '  from cpFaTongYS with(nolock)  where %s) FTYS',[Wr]);
   PSCon_Query.Open;

  Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
    'cpFaTongYS with(nolock) ',Wr),AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetcmKuCunLW(DT,UserFID: string): TFDQuery;
var
  Wr: string;
begin
 Try
//    Wr := Format('LWBG.LWBG_DT > ''%s''', [DT]);
   Wr :='KCLW_YLLX=1 or KCLW_YLLX=2';
  if (UserFID<>'') and (UserFID <> '{11111111-1111-1111-1111-111111111111}') then
    Wr := CombinAndSql(Wr,Format('KCLW.KCLW_FZGS=''%s''',[UserFID])) ;

  PSCon_Query.SQL.Clear;
  PSCon_Query.SQL.Text :=CombinQuerySql('KCLW_BH as 编号,KCLW_MC as 名称'+
        ',KCLW_YLMC as 原料名称,KCLW_ZXZ/1000 as 最小值'+
        ',KCLW_ZDZ/1000 as 最大值,LWBG_SZ2/1000 当前值,KCLW.KCLW_ID,KCLW_LWBG',
    'cmKuCunLW KCLW with(Nolock) '+
    'left join cmLiaoWeiBG LWBG with(Nolock) on KCLW.KCLW_LWBG = LWBG.LWBG_ID',
    Wr, 'order by KCLW_BH');
 PSCon_Query.Active := True;

 Result :=PSCon_Query;
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetCompany(ID:string): Variant;
begin
  Result :=Db.QueryValue('IsNull(company_Http,'''')company_Http,'+
   'IsNull(company_Port,0)company_Port',
   Format('%s..company with(Nolock)',[TConfigFile.GetAppDb]),
   Format('company_ID=''%s''',[ID]));
end;

function TPSConDataModule.GetConFenZiGS(Search: string;
  PageNo: Integer):String;
  var
   Wr,Rwr:String;
begin

 try
   Wr := 'isnull(FZGS_App,0)=1';
   if (Search <>'') then
   begin
     Wr := CombinAndSql (wr, Format('FZGS_名称 like ''%%%s%%''' ,[Search]));
		 Rwr := CombinAndSql (wr, Format('FZGS_名称 like like ''%%%s%%''' ,[Search]));
   end else
    begin
      Wr := wr;
			Rwr := wr;
	 end;

   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := Format('select top '+ intToStr(Top) +'* '+
   'from (select row_number()over(order by FZGS_名称)rownumber,'+
   '  FZGS_ID,FZGS_名称 FZGS_MC,FZGS_编号 FZGS_BH,FZGS_STOP,FZGS_App'+
   '  from ConFenZiGS with(nolock)  where %s) FZGS'+
   ' where  rownumber>'+IntToStr(Top*(PageNo - 1)),[Wr]);
   PSCon_Query.Active := True;


   Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
    'ConFenZiGS with(nolock) ',Rwr),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetCpGongChengKH(Search, FromTM, ToTM: string;
  PageNo: Integer):String;
  var
   Wr:string;
begin
   if FromTM = ToTM then
    ToTM :=DateTimeToStr(GetStrTodateTime(ToTM)+1/24);
   if Search <>'' then
    Search :=Format(' and GCKH_工程名称 like ''%%%s%%'' or GCKH_施工单位 like ''%%%s%%'''
       ,[Search,Search])
   else
     Search :='';
 Try
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := Format('select top '+ intToStr(Top) +'* '+
   'from (select row_number()over(order by GCKH_工程名称)rownumber,'+
   '  GCKH.GCKH_ID,GCKH_工程名称 GCKH_GCMC,GCKH_施工单位 GCKH_SGDW,'+
   '  sum(SCRW_计划数量) SCRW_JHSL,SUM(FTYS_运输数量)FTYS_YSSL,'+
   '  SUM(FTYS_生产数量)FTYS_SCSL from cpFaTongYS FTYS'+
   '   left join cpShengChanRW SCRW on FTYS.SCRW_ID=SCRW.SCRW_ID'+
   '   left join cpGongChengKH GCKH on SCRW.GCKH_ID=GCKH.GCKH_ID '+
   ' where FTYS_时间 between ''%s'' and ''%s'' %s '+
   '  group by GCKH.GCKH_ID,GCKH_工程名称,GCKH_施工单位'+
   '  ) GCKH '+
   ' where  rownumber>'+IntToStr(Top*(PageNo - 1)),[FromTM,ToTM,Search]);
   PSCon_Query.Active := True;


   Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
    'cpFaTongYS  FTYS left join cpShengChanRW SCRW on FTYS.SCRW_ID=SCRW.SCRW_ID '+
    'left join cpGongChengKH GCKH on SCRW.GCKH_ID=GCKH.GCKH_ID',
    format('FTYS_时间 between ''%s'' and ''%s'' %s',[FromTM,ToTM,Search]),
    'group by GCKH.GCKH_ID,GCKH_工程名称,GCKH_施工单位'),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetCpshengChanPB(SCRW_ID, UserFID: string): TFDQuery;
  var
   Wr:string;
begin
 Try
   Wr := Format('SCPB.SCRW_ID = ''%s'' ',[SCRW_ID]);

  if (UserFID <> '{11111111-1111-1111-1111-111111111111}') and (UserFID<>'')  then
    	Wr := CombinAndSql(Wr,Format('SCRW_FZGS=''%s''',[UserFID]));

   PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text :=CombinQuerySql('SCRW_编号,GCKH_工程名称,'+
    'GCKH_施工单位,SCRW_部位名称,SCRW_坍落稠度,SCPB.*',
    'cpShengChanRW SCRW with(NoLock) '+
    'left join CpshengChanPB SCPB with(NoLock) on SCRW.SCRW_ID = SCPB.SCRW_ID and SCRW.SCRW_配比次数=SCPB_NOID '+
    'left Join cpGongChengKH GCKH with(NoLock) on SCRW.GCKH_ID= GCKH.GCKH_ID',Wr);
   PSCon_Query.Open;

  Result := PSCon_Query;
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetDetail(YLPZ_ID, FromTM, ToTM,UserFID: string):String;
  var
   Wr:string;
begin
 Try
  if FromTM = ToTM then
    ToTM :=DateTimeToStr(GetStrTodateTime(ToTM)+1/24);

    Wr := Format('YLYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]);

  if UserFID <> '{11111111-1111-1111-1111-111111111111}'  then
			Wr := CombinAndSql(Wr,Format('YLPZ_ID =''%s'' and YLYS_FZGS=''%s''',[YLPZ_ID,UserFID]))
   else
			Wr := CombinAndSql(Wr,Format('YLPZ_ID=''%s''',[YLPZ_ID]));

   PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text :=CombinQuerySql('YLPZ_ID,YLYS_FZGS,YLYS_编号 YLYS_BH ,'+
      'YLYS_运输车辆 YLYS_YSCL,'+
      'YLYS_毛称时间 YLYS_MC_MTM,YLYS_皮称时间 YLYS_PC_MTM,'+
      'YLYS_毛重 YLYS_MZ,YLYS_皮重 YLYS_PZ,YLYS_验收数量 YLYS_YSSL,YLGS_名称 YLGS_MC,'+
      'YLPZ_名称 YLYS_YLMC','CmYuanLiaoYSView with(Nolock)',Wr,'order by YLYS_PC_MTM desc');
   PSCon_Query.Open;

   Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
   'CmYuanLiaoYSView with(Nolock)',Wr ),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount))
  except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetFuJiaBH(Search: string;
  PageNo: Integer):String;
  var
   Wr,Rwr:string;
begin
 Try
		if (Search <>'') then
    begin
			Search := Format('QTYQ_名称 like ''%%%s%%''',[Search]);
			Wr := CombinAndSql(Wr,Search);
    end;

    PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text :=CombinQuerySql('row_number()over(order by QTYQ_名称)rownumber,'+
      'QTYQ_名称 FJBH',
      'cpQiTaYaoQiu with(nolock)',Wr);
//    PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +' * ',
//    '('+
//      CombinQuerySql('row_number()over(order by QTYQ_名称)rownumber,'+
//      'QTYQ_名称 FJBH',
//      'cpQiTaYaoQiu with(nolock)',Wr)+
//      ' )FJBH',
//     Format('rownumber>%d',[Top*(PageNo - 1)]));
   PSCon_Query.Open;

   Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
    'cpQiTaYaoQiu with(nolock)',Wr),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount))
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetFZGSMC(UserFID: string; ISGC: Boolean): string;
begin
  try
    if ISGC then
       begin
        PSCon_Query.SQL.Clear;
        PSCon_Query.SQL.Text :=CombinQuerySql('GCKH_ID,GCKH_工程名称 GCKH_MC',
           'cpGongChengKH with(nolock)',
        Format('GCKH_ID=''%s''',[UserFID]));
        PSCon_Query.Open();
        Result := PSCon_Query.FieldByName('GCKH_MC').AsString;
      end else
      begin
        PSCon_Query.SQL.Clear;
        PSCon_Query.SQL.Text :=CombinQuerySql('FZGS_ID,FZGS_名称 FZGS_MC,'+
          'FZGS_编号 FZGS_BH,FZGS_STOP,FZGS_App','ConFenZiGS with(nolock)',
          Format('isnull(FZGS_App,0)=1 and FZGS_ID=''%s'' ',[UserFID]));
        PSCon_Query.Open();
        Result := PSCon_Query.FieldByName('FZGS_MC').AsString;
      end;

  except
   on E:Exception do
     begin
      LogI(E.ToString+'='+PSCon_Query.SQL.Text);
      Raise;
     end;
  end;
end;

function TPSConDataModule.getGCKHMC(GCKH_ID: string): string;
begin
  try
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := CombinQuerySql('GCKH_ID,GCKH_工程名称 GCKH_MC','cpGongChengKH with(nolock)',Format('GCKH_ID=''%s''',[GCKH_ID]));
   PSCon_Query.Active := True;
   Result := PSCon_Query.FieldByName('GCKH_MC').AsString;
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetGCKH_MC(FromTM, ToTM, GCKH_MC: string): string;
var
 RQWR,Wr :string;
begin
    if (FromTM = ToTM) then
    RQWR := Format('convert(char(10), FTYS_时间, 20)=''%s''', [FromTM])
  else
    RQWR := Format('FTYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]);
   FDQuery1.SQL.Clear;
  if GCKH_MC<>'' then
     FDQuery1.SQL.Text := CombinQuerySql('GCKH_工程名称 GCKH_MC','cpGongChengKH with(Nolock)',
     Format('GCKH_ID in (%s) and GCKH_工程名称 like ''%%%s%%''',[
     CombinQuerySql('GCKH_ID','cpShengChanRW with(Nolock)',
     Format('SCRW_ID in (%s)',[CombinQuerySql('SCRW_ID','cpfatongys with(Nolock)',RQWR)]))
     ,GCKH_MC]))
   else
     FDQuery1.SQL.Text := CombinQuerySql('GCKH_工程名称 GCKH_MC','cpGongChengKH with(Nolock)',
     Format('GCKH_ID in (%s)',[CombinQuerySql('GCKH_ID','cpShengChanRW with(Nolock)',
     Format('SCRW_ID in (%s)',[CombinQuerySql('SCRW_ID','cpfatongys with(Nolock)',RQWR)]))]));
     FDQuery1.Open();

  Result := GetAppDataToJson(FDQuery1.RecordCount,
    AppDataToJson(FDQuery1,FDQuery1.RecordCount));

end;

function TPSConDataModule.GetGKJLSql(FiledName, Wr: string): string;
var
  Sqlstr:string;
begin
   Sqlstr :='select GKJL_PNO,GKJL_MTM,GKJL_PL,UNIT,YLMC'+
     ',IsNull(设定值,0)设定值,IsNull(计量值,0)计量值,设定值-计量值 误差值'+
     ',case when isnull(计量值,0)-isnull(设定值,0)=0 then 0  '+
     '     when isnull(设定值,0)=0 then 0 when isnull(计量值,0)-isnull(设定值,0)<>0 '+
     '     then convert(decimal(18,2),(isnull(计量值,0)-isnull(设定值,0))/isnull(设定值,0)*100)'+
     ' end  误差率 '+
      'from ( '+
      ' SELECT GKJL_PNO,GKJL_生产时间 GKJL_MTM,GKJL_生产盘量 GKJL_PL,UNIT,'+
        FiledName+' FROM ciGongKongJL with(nolock) where '+Wr+
     ')GKJL where isnull(设定值,0)>0';

   Result :=Sqlstr;
end;

function TPSConDataModule.GetGongCKH(Search: string;
  PageNo: Integer):String;
  var
   Wr,Rwr:string;
begin
 Try
   Wr :=Format('GCKH_ID<>''%s''',['{00000000-0000-0000-0000-000000000000}']);
   if (Search <>'') then
   begin
     Wr := CombinAndSql(Wr,Format('GCKH_工程名称 like ''%%%s%%''',[Search]));
		 Rwr := CombinAndSql(Wr, Format('isnull(GCKH_工程状态,0)=0 and GCKH_工程名称 like ''%%%s%%''',[Search]));
		 end	 else
   begin
		 Wr := Wr;
		 Rwr :=CombinAndSql(Wr, 'isnull(GCKH_工程状态,0)=0') ;
   end;

   PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +' * ','('+
      CombinQuerySql('row_number()over(order by GCKH_工程名称)rownumber,'+
      'GCKH_ID,GCKH_工程名称 GCKH_MC,GCKH_施工单位 GCKH_SGDW,GCKH_施工地点 GCKH_SGDD',
      'cpGongChengKH with(nolock)',
     Format('isnull(GCKH_工程状态,0)=0 and  %s',[Wr]))+')GCKH',
     Format('rownumber>%d',[Top*(PageNo - 1)]));
   PSCon_Query.Open;

   Result := GetAppDataToJson(DB.QueryValue('count(*) rows',
    'cpGongChengKH with(nolock)',Rwr),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetGuiGBH(Search: string;
  PageNo: Integer):String;
  var
   Wr,Rwr:string;
begin
  Try

		if (Search <>'') then
    begin
			Search := Format('GGBH_规格标号 like ''%%%s%%''',[Search]);
			Wr := CombinAndSql(Wr,Search);
    end;

    PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text := CombinQuerySql('row_number()over(order by GGBH_规格标号)rownumber,'+
      ' GGBH_ID,GGBH_品种类型 GGBH_LX, GGBH_规格标号 GGBH',
      'cpGuiGeBH with(nolock)',Wr);
    //PSCon_Query.SQL.Text := //CombinQuerySql('top '+ intToStr(Top) +' * ',
   // '('+
//      CombinQuerySql('row_number()over(order by GGBH_规格标号)rownumber,'+
//      ' GGBH_ID,GGBH_品种类型 GGBH_LX, GGBH_规格标号 GGBH',
//      'cpGuiGeBH with(nolock)',Wr)+
     // ' )GGBH_',
//     Format('rownumber>%d',[Top*(PageNo - 1)]);//);
   PSCon_Query.Open;

   Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
    'cpGuiGeBH with(nolock)',Wr),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
  except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
  end;
end;

function TPSConDataModule.GetSCRWBH(ID: String;aDate: TDateTime): string;
var
  AToken,Wr,LeftSCRWBH,SQL: string;
//  ADTFormat, ASeriesNum, AConChar: string;
  ASeriesNum:Integer;
  pvSCRW_BH:Variant;
begin
 Try
   pvSCRW_BH := Db.QueryValue('[VALUE]','PushParam','id=''SCRW_BH''');
   if ID<>'{00000000-0000-0000-0000-000000000000}' then
    begin
     if Pos('/', pvSCRW_BH) > 0 then
      AToKen := '/'
     else
      if Pos(',', pvSCRW_BH) > 0 then
        AToKen := ','
      else
      if Pos(';', pvSCRW_BH) > 0 then
        AToKen := ';'
      else
      if Pos('|', pvSCRW_BH) > 0 then
        AToKen := '|';

      if length(pvSCRW_BH)>0 then
         ASeriesNum:=StrToInt(copy(pvSCRW_BH,pos(AToKen,pvSCRW_BH)+length(AToKen),1))
       else
         ASeriesNum :=3;

       LeftSCRWBH  := Copy(pvSCRW_BH,1,Pos(AToKen,pvSCRW_BH)-1);
      Wr := Format('SCRW_ID<>''{00000000-0000-0000-0000-000000000000}'' and ISNULL(SCRW_FROM,0)=0 AND '+
           ' ISNULL(SCRW_DEL,0)=0  AND SCRW_ID<>''%s'' AND SCRW_日期<=''%s'' '+
           'and SCRW_编号 LIKE ''%%%s%%''',
           [ID,FormatDateTime('YYYY-MM-DD',Date),FormatDateTime(LeftSCRWBH,Date)]);

     if (Pos('Y',pvSCRW_BH)>0) or (Pos('M',pvSCRW_BH)>0) or (Pos('D',pvSCRW_BH)>0) then
         begin
            SQL := Format('if not exists(%s) %s else %s ',[
               CombinExistSql('cpShengChanRW with(Nolock)',Wr),
               CombinQuerySql(Format('%s as SCRW_BH',
                 [FormatDateTime(LeftSCRWBH,Date)+ CreateNumStr(StrToInt64Def('0',0),ASeriesNum)
               ]),''),
              CombinQuerySql('max(SCRW_编号) SCRW_BH',
              '('+CombinQuerySql('top 1 *','cpShengChanRW with(Nolock)',Wr,
              'order by SCRW_MTM desc,SCRW_编号 desc')+')_A_')
           ]);
          Result :=AutoCode(Db.QueryValue(SQL));

        end
       else
        begin
           SQL := Format('if not exists(%s) %s else %s ',[
              CombinExistSql('cpShengChanRW with(Nolock)',
               '(ISNUMERIC(SUBSTRING(SCRW_编号, LEN(SCRW_编号), 1))=1) AND (LEN(RTRIM(SCRW_编号)) = '+
               '(select max(len(rtrim(SCRW_编号))) FROM cpShengChanRW with(Nolock) '+
               'WHERE (ISNUMERIC(SUBSTRING(SCRW_编号, LEN(SCRW_编号), 1))=1)) )'),
               CombinQuerySql(Format('%s as SCRW_BH',
                 [FormatDateTime(LeftSCRWBH,Date)+ CreateNumStr(StrToInt64Def('0',0),ASeriesNum)
               ]),''),
               CombinQuerySql('max(rtrim(SCRW_编号)) SCRW_BH',
               'cpShengChanRW with(Nolock)',
               '(ISNUMERIC(SUBSTRING(SCRW_编号, LEN(SCRW_编号), 1))=1) AND (LEN(RTRIM(SCRW_编号)) = '+
         '(select max(len(rtrim(SCRW_编号))) FROM cpShengChanRW with(Nolock) '+
         'WHERE (ISNUMERIC(SUBSTRING(SCRW_编号, LEN(SCRW_编号), 1))=1)) )')
            ]);
         Result :=AutoCode(Db.QueryValue(SQL));
        end;
    end;
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetSql(FiledName, Wr, WrRQ: string): string;
var
  Sqlstr:string;
begin
   if Wr<>'' then
     Wr := 'where isnull(设定值,0)>0 and '+Wr
     else
     Wr := 'where isnull(设定值,0)>0 ';

   Sqlstr :='select 工程名称,施工单位,YLMC,sum(设定值)设定值,sum(计量值)计量值,sum(设定值)-sum(计量值) 误差值'+
     ',case when isnull(sum(计量值),0)-isnull(sum(设定值),0)=0 then 0  '+
     '     when isnull(sum(设定值),0)=0 then 0 when isnull(sum(计量值),0)-isnull(sum(设定值),0)<>0 '+
       '     then convert(decimal(18,2),(isnull(sum(计量值),0)-isnull(sum(设定值),0))/isnull(sum(设定值),0)*100)'+
     ' end  误差率 '+
      'from ( '+
      ' SELECT UNIT,GKJL_FID,GKJL_PNO,GKJL_生产盘量,GKJL_生产日期,GKJL_生产时间,'+
        FiledName+' FROM ciGongKongJL with(nolock) where '+WrRQ+
     ')GKJL left join ciGongKongSC_Intf GKSC with(nolock) on '+
       ' GKJL.GKJL_FID=GKSC.SCID and GKJL.UNIT=GKSC.UNIT and GKJL.GKJL_生产日期=GKSC.SCRQ '+
       ' '+Wr+ ' group by 工程名称,施工单位,YLMC';

   Result :=Sqlstr;
end;

function TPSConDataModule.GetSumCmYuanLiaoYS(BegDate,GetDate,
  FZGS_ID,Search,YLPZ_MC: string;YLPZ_LX,PageNo:Integer): string;
  Var
   Wr,QWr,FromTM,ToTM,Sqlstr:string;
   I,D:Integer;
begin
   try
     if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
      Wr := CombinAndSql(Wr,Format('YLYS_FZGS=''%s''',[FZGS_ID]));

     if YLPZ_LX> 0 then
       Wr := CombinAndSql(Wr,Format('YLPZ_原料类型=%d',[YLPZ_LX]));
     if Search<>'' then
        Wr := CombinAndSql(Wr,Format('YLPZ_名称 like ''%%%s%%''',[Search]));
     if YLPZ_MC<>'' then
         Wr := CombinAndSql(Wr,Format('YLPZ_名称 = ''%s''',[YLPZ_MC]));
      Sqlstr :=  Format('if exists (%s %s', [
      'select * from tempdb.dbo.sysobjects '+
      'where id = object_id(N''tempdb..#PRO_SumCmYuanLiaoYS'') and type=''U'')'+
      ' drop table #PRO_SumCmYuanLiaoYS',
      ' create table #PRO_SumCmYuanLiaoYS '+// 创建临时表#Tmp
        '( '+
        ' FromTM datetime,'+
        ' ToTM datetime,'+
        ' 供方数量 numeric(12,3),'+
        ' 毛重数量 numeric(12,3),'+
        ' 皮重数量 numeric(12,3),'+
        ' 验收数量 numeric(12,3),'+
        ' 验收金额 numeric(18,4), '+
        ' 验收车次 int'+
        ')'
      ]);
    PSCon_Connection.ExecSQL(Sqlstr);
    D:=round(abs(GetStrTodateTime(GetDate)-GetStrTodateTime(BegDate)));
    if Now<GetStrTodateTime(GetDate)-1 then
     D := D-1;
   for I := 1 to D do
     begin
      if I=1 then
       begin
         FromTM :=BegDate;
         ToTM  :=DateTimeToStr(GetStrTodateTime(BegDate)+1);
       end else
       begin
         FromTM :=DateTimeToStr(GetStrTodateTime(BegDate)+I-1);
         ToTM :=DateTimeToStr(GetStrTodateTime(BegDate)+I);
       end;



       QWr := 'isnull(YLYS_DEL,0)=0 and (YLYS_ADD=1) or (YLYS_ADD=0 and YLYS_称验状态=3) and YLYS_审核状态<2';   // 补录或称验完成
       QWr := CombinAndSql(QWr,Format('YLYS_时间>=''%s'' and YLYS_时间<=''%s''',[FromTM,ToTM]));

       FDQuery1.SQL.Clear;
       FDQuery1.SQL.Text :=CombinQuerySql('sum(isnull(YLYS_供方数量,0))SumDateGFSL'+
        ',sum(isnull(YLYS_毛重,0)) SumDateMZSL'+
        ',sum(isnull(YLYS_皮重,0))SumDatePZSL '+
        ',sum(isnull(YLYS_验收数量,0))SumDateYSSL'+
        ',Sum(Isnull(YLYS_金额,0))SumDateYSJE'+
        ',COUNT(*)SumDateCC',
        'CmYuanLiaoYSView with(Nolock)',
         CombinAndSql(Wr,QWr));
       FDQuery1.Open;
       if not FDQuery1.IsEmpty then
       begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#PRO_SumCmYuanLiaoYS',
        'FromTM,ToTM,供方数量,毛重数量,皮重数量,验收数量,验收金额,验收车次',
        Format('''%s'',''%s'',%.3f,%.3f,%.3f,%.3f,%.4f,%d',[
         FromTM,ToTM
        ,FDQuery1.FieldByName('SumDateGFSL').AsFloat
        ,FDQuery1.FieldByName('SumDateMZSL').AsFloat
        ,FDQuery1.FieldByName('SumDatePZSL').AsFloat
        ,FDQuery1.FieldByName('SumDateYSSL').AsFloat
        ,FDQuery1.FieldByName('SumDateYSJE').AsFloat
        ,FDQuery1.FieldByName('SumDateCC').AsInteger])));
       end;
     end;

     Total_Query.SQL.Clear;
     Total_Query.SQL.Text:= CombinQuerySql(
       'sum(供方数量)供方数量,sum(毛重数量)毛重数量,sum(皮重数量)皮重数量'+
       ',sum(验收数量)验收数量,sum(验收金额)验收金额,sum(验收车次)验收车次',
       '#PRO_SumCmYuanLiaoYS');
     Total_Query.Open();

     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Text :=CombinQuerySql('top '+ intToStr(Top) +' * ',
       '('+  CombinQuerySql('row_number()over(order by FromTM )rownumber,*',
         '#PRO_SumCmYuanLiaoYS') +') PRO_SumCmYuanLiaoYS ',
       Format('rownumber>%d',[Top*(PageNo - 1)]),'Order by rownumber');;
     PSCon_Query.Open();
     PSCon_Query.Last;

   Result := GetAppDataToJson (db.QueryValue('Count(*) Rows','#PRO_SumCmYuanLiaoYS with(Nolock)'),
      AppDataToJson(Total_Query,Total_Query.RecordCount),
      AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
  except
   on E: Exception do
    begin
      Log('!'+E.ToString);
     raise;
    end;
  end;
end;

function TPSConDataModule.GetSumCmYuanLiaoYSMX(BegDate,GetDate,
  FZGS_ID,Search,YLPZ_MC: string;YLPZ_LX:Integer): TFDQuery;
  Var
    Wr,DSql:string;
begin
   try
   // 补录或称验完成
    Wr := 'isnull(YLYS_DEL,0)=0 and (YLYS_ADD=1) or (YLYS_ADD=0 and YLYS_称验状态=3) and YLYS_审核状态<2';

    Wr := CombinAndSql (Wr,Format('YLYS_时间 between ''%s'' and ''%s''',[BegDate,GetDate]));

    if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
      Wr := CombinAndSql(Wr,Format('YLYS_FZGS=''%s''',[FZGS_ID]));

    if Search<>'' then
       Wr := CombinAndSql(Wr,
       Format('YLYS_编号 like ''%%%s%%''  or YLYS_运输车辆 like ''%%%s%%'''+
       ' or YLPZ_名称 like ''%%%s%%'' or YLGS_名称 like ''%%%s%%''',
       [Search,Search,Search,Search]));
    if YLPZ_LX> 0 then
       Wr := CombinAndSql(Wr,Format('YLPZ_原料类型=%d',[YLPZ_LX]));

     if YLPZ_MC<>'' then
         Wr := CombinAndSql(Wr,Format('YLPZ_名称 = ''%s''',[YLPZ_MC]));

       DSql :=Format('insert into #CmYuanLiaoYSView with (rowlock) '+
         '(YLPZ_品种名称,YLPZ_规格等级,当日供方数量,当日毛重,当日皮重,当日验收数量,'+
         '当日金额,当日车次) %s ',
      [CombinQuerySql('isnull(YLPZ_品种名称,'''')YLPZ_品种名称'+
       ' ,isnull(YLPZ_规格等级,'''')YLPZ_规格等级'+
       ' ,sum(isnull(YLYS_供方数量,0)) 当日供方数量'+
       ' ,sum(isnull(YLYS_毛重,0))当日毛重'+
       ' ,sum(isnull(YLYS_皮重,0))当日皮重'+
       ' ,sum(isnull(YLYS_验收数量,0))当日验收数量'+
       ' ,Sum(Isnull(YLYS_金额,0))当日金额,COUNT(*) 当日车次',
       'CmYuanLiaoYSView',Wr, //YLPZ_ID
       'group by YLPZ_品种名称,YLPZ_规格等级')]);

     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Add('execute PRO_SumCmYuanLiaoYSMX :DSql');
       PSCon_Query.Params.ParamValues['DSql']:= DSql;
     PSCon_Query.Active := True;

  except
   on E: Exception do
    begin
      Log('!'+E.ToString);
     raise;
    end;
  end;

   Result := PSCon_Query;
end;

function TPSConDataModule.GetSumFaTongTJMX2(FormTm, ToTM, GCKH_ID,
  FZGS_ID: string;PageNo:Integer=0): string;
  var
   Wr:string;
begin
   Wr :='(ISNULL(FTYS_DEL, 0) = 0) AND (FTYS_签发审核 > 0)';

  if FormTm = ToTM then
    Wr := CombinAndSql(Wr,Format('FTYS_日期=''%s''',[FormTm]))
   else
    Wr := CombinAndSql(Wr,Format('FTYS_日期 between ''%s'' and ''%s'' ',[FormTm,ToTM]));

   if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
     Wr := CombinAndSql(Wr,Format('FTYS_FZGS=''%s''',[FZGS_ID]));

   if GCKH_ID<>'' then
     Wr := CombinAndSql(Wr,Format('SCRW.GCKH_ID=''%s''',[GCKH_ID]));

  try
     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +' * ',
    '('+
       CombinQuerySql('row_number()over(order by SCRW_规格标号_+SCRW_附加标号)rownumber,'+
       'SCRW.GCKH_ID,GCKH_工程名称 as 工程名称,GCKH_施工单位 as 施工单位,'+
       'FTYS_输送方式,SCRW_规格标号_+SCRW_附加标号 as 砼标号,sum(isnull(FTYS_商砼数量2,0)) as 砼统计,'+
       'sum(isnull(FTYS_泵送数量2,0)) as 砼浆统计,sum(isnull(FTYS_泵浆数量2,0)) as 泵送统计,'+
       'Count(*) 累计车次',
       'cpFaTongYS  FTYS with(nolock) '+
       'left join cpShengChanRW SCRW with(Nolock) on FTYS.SCRW_ID= SCRW.SCRW_ID '+
       'left join cpGongChengKH GCKH with(Nolock) on SCRW.GCKH_ID= GCKH.GCKH_ID',Wr,
     'group by SCRW.GCKH_ID,GCKH_工程名称,GCKH_施工单位,FTYS_输送方式,SCRW_规格标号_+SCRW_附加标号')+
      ' )YLYS',
     Format('rownumber>%d',[Top*(PageNo - 1)]),'Order by rownumber');
   PSCon_Query.Open;

  except
   on E: Exception do
    begin
      Log('!'+E.ToString);
     raise;
    end;
  end;
  Result := GetAppDataToJson(10,
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
end;

function TPSConDataModule.GetSumFatongtaizhang(BegDate, GetDate, FZGS_ID,
  Search, GCKH_MC: string; ISGC, IsSH,PageNo: Integer): string;
  Var
   Wr,QWr,FromTM,ToTM,Sqlstr:string;
   I,D:Integer;
begin
   try
    if (ISGC>0) and (FZGS_ID<>'') then
      Wr := CombinAndSql(Wr,Format('GCKH.GCKH_ID=''%s''',[FZGS_ID]))
    else
    begin
     if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
      Wr := CombinAndSql(Wr,Format('FTYS_FZGS=''%s''',[FZGS_ID]));
    end;
    if GCKH_MC<>'' then
       Wr := CombinAndSql ( Wr, Format('GCKH_工程名称 like ''%%%s%%''',[GCKH_MC]));
    if Search<>'' then
      Wr := CombinAndSql(Wr,
        Format('GCKH_工程名称 like ''%%%s%%'' or GCKH_施工单位 like ''%%%s%%'' '+
        'or SCRW_编号 like ''%%%s%%'' or SCRW_规格标号_+SCRW_附加标号 like ''%%%s%%''',
        [Search,Search,Search,Search]));

      Sqlstr :=  Format('if exists (%s %s', [
      'select * from tempdb.dbo.sysobjects '+
      'where id = object_id(N''tempdb..#GetSumFatongtaizhang'') and type=''U'')'+
      ' drop table #GetSumFatongtaizhang',
      ' create table #GetSumFatongtaizhang '+// 创建临时表#Tmp
        '( '+
        ' FromTM datetime,'+
        ' ToTM datetime,'+
        ' FTYS_YSSL  numeric(20,4),'+
        ' FTYS_BSSL   numeric(20,4),' +
        ' 累计车次 int'+
        ')'
      ]);
    PSCon_Connection.ExecSQL(Sqlstr);
    D:=round(abs(GetStrTodateTime(GetDate)-GetStrTodateTime(BegDate)));
    if Now<GetStrTodateTime(GetDate)-1 then
     D := D-1;
   for I := 1 to D do
     begin
      if I=1 then
       begin
         FromTM :=BegDate;
         ToTM  :=DateTimeToStr(GetStrTodateTime(BegDate)+1);
       end else
       begin
         FromTM :=DateTimeToStr(GetStrTodateTime(BegDate)+I-1);
         ToTM :=DateTimeToStr(GetStrTodateTime(BegDate)+I);
       end;

      if IsSH>0 then
        begin
         QWr :=format('FTYS_时间>=''%s'' and FTYS_时间<= ''%s'' and '+
         '(ISNULL(FTYS_DEL, 0) = 0) AND (FTYS_回单审核>=2)',[FromTM,ToTM]);
        end else
        begin
         QWr :=format('FTYS_时间>=''%s'' and FTYS_时间<= ''%s'' and '+
          '(ISNULL(FTYS_DEL, 0) = 0) AND (FTYS_签发审核 =1)',[FromTM,ToTM]);
        end;

      FDQuery1.SQL.Clear;
       FDQuery1.SQL.Text :=  CombinQuerySql(
         'sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,'+
         'sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,Count(*) 累计车次',
         'cpfatongysView FTYS with(Nolock) '+
         'left join cpGongChengKH GCKH with(Nolock) on FTYS.GCKH_ID=GCKH.GCKH_ID '+
         'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',
         CombinAndSql(Wr,QWr));
       FDQuery1.Open;
       if not FDQuery1.IsEmpty then
       begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#GetSumFatongtaizhang',
        'FromTM,ToTM,FTYS_YSSL,FTYS_BSSL,累计车次',
        Format('''%s'',''%s'',%.4f,%.4f,%d',[
         FromTM,ToTM
        ,FDQuery1.FieldByName('FTYS_YSSL').AsFloat
        ,FDQuery1.FieldByName('FTYS_BSSL').AsFloat
        ,FDQuery1.FieldByName('累计车次').AsInteger])));
       end;
     end;

    if (D=0) and (HoursBetween(GetStrTodateTime(GetDate),GetStrTodateTime(BegDate))<=24) then
     begin
       FromTM :=BegDate;
       ToTM := GetDate;
       if IsSH>0 then
        begin
         QWr :=format('FTYS_时间>=''%s'' and FTYS_时间<= ''%s'' and '+
         '(ISNULL(FTYS_DEL, 0) = 0) AND (FTYS_回单审核>=2)',[FromTM,ToTM]);
        end else
        begin
         QWr :=format('FTYS_时间>=''%s'' and FTYS_时间<= ''%s'' and '+
          '(ISNULL(FTYS_DEL, 0) = 0) AND (FTYS_签发审核 =1)',[FromTM,ToTM]);
        end;

      FDQuery1.SQL.Clear;
       FDQuery1.SQL.Text :=  CombinQuerySql(
         'sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,'+
         'sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,Count(*) 累计车次',
         'cpfatongysView FTYS with(Nolock) '+
         'left join cpGongChengKH GCKH with(Nolock) on FTYS.GCKH_ID=GCKH.GCKH_ID '+
         'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',
         CombinAndSql(Wr,QWr));
       FDQuery1.Open;
       if not FDQuery1.IsEmpty then
       begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#GetSumFatongtaizhang',
        'FromTM,ToTM,FTYS_YSSL,FTYS_BSSL,累计车次',
        Format('''%s'',''%s'',%.4f,%.4f,%d',[
         FromTM,ToTM
        ,FDQuery1.FieldByName('FTYS_YSSL').AsFloat
        ,FDQuery1.FieldByName('FTYS_BSSL').AsFloat
        ,FDQuery1.FieldByName('累计车次').AsInteger])));
       end;
     end;
     Total_Query.SQL.Clear;
     Total_Query.SQL.Text:= CombinQuerySql(
       'sum(FTYS_YSSL)FTYS_YSSL,sum(FTYS_BSSL)FTYS_BSSL,sum(累计车次)累计车次',
       '#GetSumFatongtaizhang');
     Total_Query.Open();

     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Text :='select top '+ intToStr(Top) +'* '+
       'from (select row_number()over(order by FromTM)rownumber,*'+
       '  from #GetSumFatongtaizhang with(nolock)) FZGS'+
       ' where  rownumber>'+IntToStr(Top*(PageNo - 1));
     PSCon_Query.Open();
     PSCon_Query.Last;

   Result := GetAppDataToJson (db.QueryValue('Count(*)','#GetSumFatongtaizhang'),
      AppDataToJson(Total_Query,Total_Query.RecordCount),
      AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
  except
   on E: Exception do
    begin
      Log('!'+E.ToString);
     raise;
    end;
  end;

end;

function TPSConDataModule.GetSumFaTongTJ(BegDate,GetDate,FZGS_ID,Search,GCKH_MC:string;
   ISGC,IsSH,PageNo:Integer): string;
  Var
   Wr,QWr,FromTM,ToTM,Sqlstr:string;
   I,D:Integer;
begin
   try
    if (ISGC>0) and (FZGS_ID<>'') then
      Wr := CombinAndSql(Wr,Format('GCKH.GCKH_ID=''%s''',[FZGS_ID]))
    else
    begin
     if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
      Wr := CombinAndSql(Wr,Format('FTYS_FZGS=''%s''',[FZGS_ID]));
    end;
    if GCKH_MC<>'' then
       Wr := CombinAndSql ( Wr, Format('GCKH_工程名称 like ''%%%s%%''',[GCKH_MC]));
    if Search<>'' then
      Wr := CombinAndSql(Wr,
        Format('GCKH_工程名称 like ''%%%s%%'' or GCKH_施工单位 like ''%%%s%%'' '+
        'or SCRW_编号 like ''%%%s%%'' or SCRW_规格标号_+SCRW_附加标号 like ''%%%s%%''',
        [Search,Search,Search,Search]));

      Sqlstr :=  Format('if exists (%s %s', [
      'select * from tempdb.dbo.sysobjects '+
      'where id = object_id(N''tempdb..#PRO_SumFaTongTJ'') and type=''U'')'+
      ' drop table #PRO_SumFaTongTJ',
      ' create table #PRO_SumFaTongTJ '+// 创建临时表#Tmp
        '( '+
        ' FromTM datetime,'+
        ' ToTM datetime,'+
        ' FTYS_YSSL  numeric(20,4),'+
        ' FTYS_BSSL   numeric(20,4),' +
        ' 累计车次 int'+
        ')'
      ]);
    PSCon_Connection.ExecSQL(Sqlstr);
    D:=round(abs(GetStrTodateTime(GetDate)-GetStrTodateTime(BegDate)));
    if Now<GetStrTodateTime(GetDate)-1 then
     D := D-1;
   for I := 1 to D do
     begin
      if I=1 then
       begin
         FromTM :=BegDate;
         ToTM  :=DateTimeToStr(GetStrTodateTime(BegDate)+1);
       end else
       begin
         FromTM :=DateTimeToStr(GetStrTodateTime(BegDate)+I-1);
         ToTM :=DateTimeToStr(GetStrTodateTime(BegDate)+I);
       end;

      if IsSH>0 then
        begin
         QWr :=format('FTYS_审核时间>=''%s'' and FTYS_审核时间<= ''%s'' and '+
         '(ISNULL(FTYS_DEL, 0) = 0) AND (FTYS_回单审核>=2)',[FromTM,ToTM]);
        end else
        begin
         QWr :=format('FTYS_时间>=''%s'' and FTYS_时间<= ''%s'' and '+
          '(ISNULL(FTYS_DEL, 0) = 0) AND (FTYS_签发审核 =1)',[FromTM,ToTM]);
        end;

      FDQuery1.SQL.Clear;
       FDQuery1.SQL.Text :=  CombinQuerySql(
         'sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,'+
         'sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,Count(*) 累计车次',
         'cpfatongysView FTYS with(Nolock) '+
         'left join cpGongChengKH GCKH with(Nolock) on FTYS.GCKH_ID=GCKH.GCKH_ID '+
         'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',
         CombinAndSql(Wr,QWr));
       FDQuery1.Open;
       if not FDQuery1.IsEmpty then
       begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#PRO_SumFaTongTJ',
        'FromTM,ToTM,FTYS_YSSL,FTYS_BSSL,累计车次',
        Format('''%s'',''%s'',%.4f,%.4f,%d',[
         FromTM,ToTM
        ,FDQuery1.FieldByName('FTYS_YSSL').AsFloat
        ,FDQuery1.FieldByName('FTYS_BSSL').AsFloat
        ,FDQuery1.FieldByName('累计车次').AsInteger])));
       end;
     end;

    if (D=0) and (HoursBetween(GetStrTodateTime(GetDate),GetStrTodateTime(BegDate))<=24) then
     begin
       FromTM :=BegDate;
       ToTM := GetDate;
       if IsSH>0 then
        begin
         QWr :=format('FTYS_审核时间>=''%s'' and FTYS_审核时间<= ''%s'' and '+
         '(ISNULL(FTYS_DEL, 0) = 0) AND (FTYS_回单审核>=2)',[FromTM,ToTM]);
        end else
        begin
         QWr :=format('FTYS_时间>=''%s'' and FTYS_时间<= ''%s'' and '+
          '(ISNULL(FTYS_DEL, 0) = 0) AND (FTYS_签发审核 =1)',[FromTM,ToTM]);
        end;

      FDQuery1.SQL.Clear;
       FDQuery1.SQL.Text :=  CombinQuerySql(
         'sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,'+
         'sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,Count(*) 累计车次',
         'cpfatongysView FTYS with(Nolock) '+
         'left join cpGongChengKH GCKH with(Nolock) on FTYS.GCKH_ID=GCKH.GCKH_ID '+
         'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',
         CombinAndSql(Wr,QWr));
       FDQuery1.Open;
       if not FDQuery1.IsEmpty then
       begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#PRO_SumFaTongTJ',
        'FromTM,ToTM,FTYS_YSSL,FTYS_BSSL,累计车次',
        Format('''%s'',''%s'',%.4f,%.4f,%d',[
         FromTM,ToTM
        ,FDQuery1.FieldByName('FTYS_YSSL').AsFloat
        ,FDQuery1.FieldByName('FTYS_BSSL').AsFloat
        ,FDQuery1.FieldByName('累计车次').AsInteger])));
       end;
     end;
     Total_Query.SQL.Clear;
     Total_Query.SQL.Text:= CombinQuerySql(
       'sum(FTYS_YSSL)FTYS_YSSL,sum(FTYS_BSSL)FTYS_BSSL,sum(累计车次)累计车次',
       '#PRO_SumFaTongTJ');
     Total_Query.Open();

     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Text := 'select top '+ intToStr(Top) +'* '+
       'from (select row_number()over(order by FromTM)rownumber,*'+
       '  from #PRO_SumFaTongTJ with(nolock)) FZGS'+
       ' where  rownumber>'+IntToStr(Top*(PageNo - 1));
     PSCon_Query.Open();
     PSCon_Query.Last;

   Result := GetAppDataToJson (db.QueryValue('Count(*)','#PRO_SumFaTongTJ'),
      AppDataToJson(Total_Query,Total_Query.RecordCount),
      AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
  except
   on E: Exception do
    begin
      Log('!'+E.ToString);
     raise;
    end;
  end;
end;

function TPSConDataModule.GetSumFaTongTJMX(BegDate,GetDate, FZGS_ID,Search,GCKH_MC: string;
  ISGC,IsSH: Integer): string;
  Var
   Wr,DWr,MWr,ISql,DSql:string;
begin
   try
    if IsSH>0 then
    begin
     DWr :=format('FTYS_审核时间>=''%s'' and FTYS_审核时间<= ''%s'' and '+
     '(ISNULL(FTYS_DEL,0) = 0) AND (FTYS_回单审核>=2)',[BegDate,GetDate]);

     MWr :=Format('dateName(year,FTYS_审核时间)=DateName(year,''%s'') '+
       'and DateName(month,FTYS_审核时间)=DateName(month,''%s'') '+
       'and (ISNULL(FTYS_DEL,0) = 0) AND (FTYS_回单审核>=2)',[GetDate,GetDate]);
    end else
    begin
     DWr :=format('FTYS_时间>=''%s'' and FTYS_时间<= ''%s'' and '+
      '(ISNULL(FTYS_DEL,0) = 0) AND (FTYS_签发审核 =1)',[BegDate,GetDate]);

      MWr :=Format('dateName(year,FTYS_日期)=DateName(year,''%s'') '+
       'and DateName(month,FTYS_日期)=DateName(month,''%s'') '+
       'and (ISNULL(FTYS_DEL,0) = 0) AND (FTYS_签发审核 =1)',
       [GetDate,GetDate]);
    end;

    if (ISGC>0) and (FZGS_ID<>'') then
      Wr := CombinAndSql(Wr,Format('GCKH.GCKH_ID=''%s''',[FZGS_ID]))
    else
    begin
     if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
      Wr := CombinAndSql(Wr,Format('FTYS_FZGS=''%s''',[FZGS_ID]));
    end;

    if Search<>'' then
      Wr := CombinAndSql(Wr,
        Format('GCKH_工程名称 like ''%%%s%%'' or GCKH_施工单位 like ''%%%s%%'' '+
        'or SCRW_编号 like ''%%%s%%'' or SCRW_规格标号_+SCRW_附加标号 like ''%%%s%%''',
        [Search,Search,Search,Search]));
     if GCKH_MC<>'' then
       Wr := CombinAndSql ( Wr, Format('GCKH_工程名称 like ''%%%s%%''',[GCKH_MC]));
    with PSCon_Query do
    begin
      sql.Clear;
      sql.text:= Format('%s If Object_Id(''Tempdb.dbo.%s'') Is Not Null begin %s end %s',
        ['declare @GCKH_ID varchar(38), @工程名称 varchar(220),@施工单位 varchar(220),'+
        '@当日计划 numeric(18,4),@本月计划 numeric(18,4),@当日砼 numeric(18,4),'+
        '@当日泵送 numeric(18,4),@当日砼浆 numeric(18,4),@当日车次 int,@本月砼 numeric(18,4),'+
        '@本月砼浆 numeric(18,4),@本月泵送 numeric(18,4),@当月车次 int',
        '##CPFaTongTJMXView',
        Format('drop table %s', ['##CPFaTongTJMXView']),
        ' Create table ##CPFaTongTJMXView( '+
        '  GCKH_ID varchar(38) not null,'+
        '  工程名称 varchar(220) DEFAULT '''','+
        '  施工单位 varchar(220) DEFAULT '''','+
       // '  当日计划 numeric(18,4) DEFAULT 0,'+
       // '  本月计划 numeric(18,4) DEFAULT 0,'+
        '  当日砼 numeric(18,4) DEFAULT 0,'+
        '  当日泵送 numeric(18,4) DEFAULT 0,'+
        '  当日砼浆 numeric(18,4) DEFAULT 0,'+
        '  当日车次 int DEFAULT 0,'+
        '  本月砼 numeric(18,4) DEFAULT 0,'+
        '  本月砼浆 numeric(18,4) DEFAULT 0,'+
        '  本月泵送 numeric(18,4) DEFAULT 0,'+
        '  当月车次 int DEFAULT 0 '+
        ' constraint PK_CPFaTongTJMX_View_ primary key nonclustered (GCKH_ID))'
       ]);
      ExecSQL;
    end;

      ISql :=Format('insert into ##CPFaTongTJMXView with (rowlock) '+
         '(GCKH_ID,工程名称,施工单位) %s ',
      [CombinQuerySql('GCKH.GCKH_ID,GCKH_工程名称 as 工程名称,GCKH_施工单位 as 施工单位',
       'cpFaTongYS FTYS with(nolock) '+
       ' left join cpShengChanRW SCRW with(Nolock) on FTYS.SCRW_ID= SCRW.SCRW_ID'+
       ' left join cpGongChengKH GCKH with(Nolock) on SCRW.GCKH_ID= GCKH.GCKH_ID',CombinAndSql(Wr,Dwr),
       'group by GCKH.GCKH_ID,GCKH_工程名称,GCKH_施工单位')]);

     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Text := ISql;
     PSCon_Query.ExecSQL;

      DSql:=CombinQuerySql('GCKH.GCKH_ID,GCKH_工程名称 as 工程名称,GCKH_施工单位 as 施工单位,'+
      'sum(isnull(FTYS_商砼数量2,0)) as 当日砼,sum(isnull(FTYS_泵送数量2,0)) as 当日砼浆,'+
      'sum(isnull(FTYS_泵浆数量2,0)) as 当日泵送,count(*) 当日车次',
      'cpFaTongYS FTYS with(nolock)'+
      ' left join cpShengChanRW SCRW with(Nolock) on FTYS.SCRW_ID= SCRW.SCRW_ID'+
      ' left join cpGongChengKH GCKH with(Nolock) on SCRW.GCKH_ID= GCKH.GCKH_ID',CombinAndSql(Wr,Dwr),
      'group by GCKH.GCKH_ID,GCKH_工程名称,GCKH_施工单位');

      FDQuery1.SQL.Clear;
      FDQuery1.SQL.Text := DSql;
      FDQuery1.Open();

      if FDQuery1.RecordCount>0 then
      begin
       FDQuery1.First;
       while not FDQuery1.Eof do
       begin
          with PSCon_Command do
          begin
            CommandText.Text :=CombinUpdateSql('##CPFaTongTJMXView',
            '当日砼=:当日砼,当日泵送=:当日泵送,当日砼浆=:当日砼浆,当日车次=:当日车次',
            'GCKH_ID=:GCKH_ID');
            Params.ParamValues['当日砼']:=FDQuery1.FieldByName('当日砼').AsFloat;
            Params.ParamValues['当日泵送']:=FDQuery1.FieldByName('当日泵送').AsFloat;;
            Params.ParamValues['当日砼浆']:=FDQuery1.FieldByName('当日砼浆').AsFloat;;
            Params.ParamValues['当日车次']:=FDQuery1.FieldByName('当日车次').AsFloat;;
            Params.ParamValues['GCKH_ID']:=FDQuery1.FieldByName('GCKH_ID').AsString;;
            Execute();
          end;
         FDQuery1.Next;
       end;
      end;
      //D计划
     DSql:=CombinQuerySql('GCKH.GCKH_ID,GCKH_工程名称 as 工程名称,GCKH_施工单位 as 施工单位,'+
      'sum(isnull(FTYS_商砼数量2,0)) as 本月砼,sum(isnull(FTYS_泵送数量2,0)) as 本月砼浆,'+
      'sum(isnull(FTYS_泵浆数量2,0)) as 本月泵送,count(*) 当月车次',
      'cpFaTongYS FTYS with(nolock)'+
      ' left join cpShengChanRW SCRW with(Nolock) on FTYS.SCRW_ID= SCRW.SCRW_ID'+
      ' left join cpGongChengKH GCKH with(Nolock) on SCRW.GCKH_ID= GCKH.GCKH_ID',CombinAndSql(Wr,Mwr),
      'group by GCKH.GCKH_ID,GCKH_工程名称,GCKH_施工单位');

      FDQuery1.SQL.Clear;
      FDQuery1.SQL.Text := DSql;
      FDQuery1.Open();

      if FDQuery1.RecordCount>0 then
      begin
       FDQuery1.First;
       while not FDQuery1.Eof do
       begin
          with PSCon_Command do
          begin
            CommandText.Text :=CombinUpdateSql('##CPFaTongTJMXView',
            '本月砼=:本月砼,本月泵送=:本月泵送,本月砼浆=:本月砼浆,当月车次=:当月车次',
            'GCKH_ID=:GCKH_ID');
            Params.ParamValues['本月砼']:=FDQuery1.FieldByName('本月砼').AsFloat;
            Params.ParamValues['本月泵送']:=FDQuery1.FieldByName('本月泵送').AsFloat;;
            Params.ParamValues['本月砼浆']:=FDQuery1.FieldByName('本月砼浆').AsFloat;;
            Params.ParamValues['当月车次']:=FDQuery1.FieldByName('当月车次').AsFloat;;
            Params.ParamValues['GCKH_ID']:=FDQuery1.FieldByName('GCKH_ID').AsString;;
            Execute();
          end;
         FDQuery1.Next;
       end;
      end;


        Total_Query.SQL.Clear;
        Total_Query.SQL.Text :=  CombinQuerySql(
       'sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,'+
       'sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,Count(*) 累计车次',
       'cpfatongysView FTYS with(Nolock) '+
       'left join cpGongChengKH GCKH with(Nolock) on FTYS.GCKH_ID=GCKH.GCKH_ID '+
       'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',
       CombinAndSql(Wr,Dwr));
       Total_Query.Open;


     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Text :=CombinQuerySql('*','##CPFaTongTJMXView');
     PSCon_Query.Open();

  except
   on E: Exception do
    begin
      Log('!'+E.ToString);
     raise;
    end;
  end;
//   Result := PSCon_Query;
   Result := GetAppDataToJson(PSCon_Query.RecordCount,
      AppDataToJson(Total_Query,Total_Query.RecordCount),
      AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
end;

function TPSConDataModule.GetSumGongKongJL(FormTm,ToTM,FZGS_ID,Search,SCMS,GCKH_MC:String): String;
var
  Wr,SCWr,WrRQ, Sqlstr, Ord: string;
  SFromTM,SToTM :TDateTime;
  I,D:Integer;
begin
  try
   if (Search<>'') then
   begin
    if (pos('C',UpperCase(Search))>0) or (pos('M',UpperCase(Search))>0) then
    begin
     Wr :=Format('规格标号 like ''%%%s%%''',[Search]);
    end else
    begin
     Wr :=Format('工程名称 like ''%%%s%%'' '+
     'or 施工单位 like ''%%%s%%'' or 部位名称 like ''%%%s%%'' '+
     'or 任务编号 like ''%%%s%%''',[Search,Search,Search,Search]);
    end;
   end;

   if SCMS<>'' then
   begin
      Wr :=CombinAndSql(Wr,Format('生产模式=''%s''',[SCMS]));
   end;
   if GCKH_MC<>'' then
    begin
      Wr :=CombinAndSql(Wr,Format('工程名称=''%s''',[GCKH_MC]));
   end;

    if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
   begin
     Wr := CombinAndSql(Wr,Format('GKSC_FZGS=''%s''',[FZGS_ID]));
     SCWr :=CombinAndSql(SCWr,Format('GKSC_FZGS=''%s''',[FZGS_ID]));
   end;

   Sqlstr :=  Format('if exists (%s %s', [
      'select * from tempdb.dbo.sysobjects '+
      'where id = object_id(N''tempdb..#PRO_SumGongKongJL'') and type=''U'') '+
      ' drop table #PRO_SumGongKongJL',
       'create table #PRO_SumGongKongJL '+   // 创建临时表#Tmp
        '( '+
        ' FromTM datetime,'+
        ' ToTM datetime,'+
        ' GKJL_YSSL  numeric(20,4), '+
        ' 累计车次   int' +
        ')'
      ]);
    PSCon_Connection.ExecSQL(Sqlstr);
      D:=round(abs(GetStrTodateTime(ToTM)-GetStrTodateTime(FormTm)));
    if Now<GetStrTodateTime(ToTM)-1 then
      D := D-1;
   for I := 1 to D do
     begin
      if I=1 then
       begin
         SFromTM :=GetStrTodateTime(FormTm);
         SToTM  := GetStrTodateTime(FormTm)+1;
       end else
       begin
         SFromTM := GetStrTodateTime(FormTm)+I-1;
         SToTM :=GetStrTodateTime(FormTm)+I;
       end;
       if (FormTm = ToTM)  then
          WrRQ := Format('convert(char(10), SCTM, 20)=''%s''', [DateTimeToStr(SFromTM)])
        else
          WrRQ := FromToWhereSQL(DateTimeToStr(SFromTM),
            DateTimeToStr(SToTM-1/60/24), 'SCTM');

       FDQuery1.SQL.Clear;
       FDQuery1.SQL.Text := CombinQuerySql(
        'sum(isnull(生产数量,0)) as GKJL_YSSL,Count(*) 累计车次',
        'CiGongKongSC_intf with(Nolock)',
         CombinAndSql(Wr,StringReplace(WrRQ,'/','-',[rfReplaceAll])));
       FDQuery1.Open();

       if not FDQuery1.IsEmpty then
       begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#PRO_SumGongKongJL',
        'FromTM,ToTM,GKJL_YSSL,累计车次',
        Format('''%s'',''%s'',%.4f,%d',[
         DateTimeToStr(SFromTM),DateTimeToStr(SToTM)
        ,FDQuery1.FieldByName('GKJL_YSSL').AsFloat
        ,FDQuery1.FieldByName('累计车次').AsInteger])));
       end;
     end;

     if (D=0) and (HoursBetween(GetStrTodateTime(ToTM),GetStrTodateTime(FormTm))<=24) then
     begin
        SFromTM :=GetStrTodateTime(FormTm);
        SToTM :=GetStrTodateTime(ToTM);

        if (FormTm = ToTM)  then
          WrRQ := Format('convert(char(10), SCTM, 20)=''%s''', [DateTimeToStr(SFromTM)])
        else
          WrRQ := FromToWhereSQL(DateTimeToStr(SFromTM),DateTimeToStr(SToTM), 'SCTM');

       FDQuery1.SQL.Clear;
       FDQuery1.SQL.Text := CombinQuerySql(
        'sum(isnull(生产数量,0)) as GKJL_YSSL,Count(*) 累计车次',
        'CiGongKongSC_intf with(Nolock)',
         CombinAndSql(Wr, StringReplace(WrRQ,'/','-',[rfReplaceAll])));
       FDQuery1.Open();
       if not FDQuery1.IsEmpty then
       begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#PRO_SumGongKongJL',
        'FromTM,ToTM,GKJL_YSSL,累计车次',
        Format('''%s'',''%s'',%.4f,%d',[
         DateTimeToStr(SFromTM),DateTimeToStr(SToTM)
        ,FDQuery1.FieldByName('GKJL_YSSL').AsFloat
        ,FDQuery1.FieldByName('累计车次').AsInteger])));
       end;
     end;

     Total_Query.SQL.Clear;
     Total_Query.SQL.Text:= CombinQuerySql(
       'sum(GKJL_YSSL)GKJL_YSSL,sum(累计车次)累计车次',
       '#PRO_SumGongKongJL');
     Total_Query.Open();

     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Text := CombinQuerySql('*','#PRO_SumGongKongJL');
     PSCon_Query.Open();

     PSCon_Query.Last;
     Result := GetAppDataToJson (PSCon_Query.RecordCount,
      AppDataToJson(Total_Query,Total_Query.RecordCount),
      AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
  except
   on E: Exception do
    begin
      Log('!'+E.ToString);
     raise;
    end;
  end;
end;

function TPSConDataModule.GetSumGongKongJLMX(FormTm, ToTM, FZGS_ID,Search,SCMS,GCKH_MC: string): string;
var
  Wr,SCWr,WrRQ, Sqlstr, Ord: string;
  FiledName:string;
  I:Integer;
begin
 try

   if (Search<>'') then
   begin
    if (pos('C',UpperCase(Search))>0) or (pos('M',UpperCase(Search))>0) then
    begin
     Wr :=Format('规格标号 like ''%%%s%%''',[Search]);
     SCWr :=Format('规格标号 like ''%%%s%%''',[Search]);
    end else
    begin
     Wr :=Format('工程名称 like ''%%%s%%'' '+
     'or 施工单位 like ''%%%s%%'' or 部位名称 like ''%%%s%%'' '+
     'or 任务编号 like ''%%%s%%''',[Search,Search,Search,Search]);
     SCWr :=CombinAndSql(Wr,SCWr);
    end;
   end;
   if SCMS<>'' then
   begin
      Wr :=CombinAndSql(Wr,Format('生产模式=''%s''',[SCMS]));
      SCWr :=CombinAndSql(Wr,Format('生产模式=''%s''',[SCMS]));
   end;
   if GCKH_MC<>'' then
    begin
      Wr :=CombinAndSql(Wr,Format('工程名称=''%s''',[GCKH_MC]));
      SCWr :=CombinAndSql(Wr,Format('工程名称=''%s''',[GCKH_MC]));
   end;

   if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
   begin
     Wr := CombinAndSql(Wr,Format('GKSC_FZGS=''%s''',[FZGS_ID]));
     SCWr :=CombinAndSql(SCWr,Format('GKSC_FZGS=''%s''',[FZGS_ID]));
   end;

   Total_Query.SQL.Clear;
   Total_Query.SQL.Text := CombinQuerySql(
    'sum(isnull(生产数量,0)) as FTYS_YSSL,Count(*) 累计车次',
    'CiGongKongSC_intf with(Nolock)',
     CombinAndSql(SCWr,FromToWhereSQL(FormTm, ToTM, 'SCTM')));
   Total_Query.Open();

   FDQuery1.SQL.Clear;
   FDQuery1.SQL.Text := CombinQuerySql(
    '工程名称,施工单位',
    'CiGongKongSC_intf with(Nolock)',
     CombinAndSql(Wr,FromToWhereSQL(FormTm, ToTM, 'SCTM')),
     'Group by 工程名称,施工单位 Order by 工程名称,施工单位');
   FDQuery1.Open();


  if (FormTm = ToTM)  then
    WrRQ := CombinAndSql(WrRQ, Format('convert(char(10), GKJL_生产时间, 20)=''%s''', [FormTm]))
  else
    WrRQ := CombinAndSql(WrRQ, FromToWhereSQL(FormTm, ToTM, 'GKJL_生产时间'));

   Sqlstr :=  Format('if exists (%s %s', [
      'select * from tempdb.dbo.sysobjects '+
      'where id = object_id(N''tempdb..#GKJLSum'') and type=''U'') drop table #GKJLSum',
       'create table #GKJLSum '+   // 创建临时表#Tmp
        '( '+
        ' 工程名称 varchar(320) not null,'+
        ' 施工单位 varchar(320) not null,'+
        ' YLMC   varchar(100) not null,'+//创建列ID,并且每次新增一条记录就会加1
        ' 设定值  numeric(20,4), '+
        ' 计量值   numeric(20,4), ' +
        ' 误差值   numeric(20,4), ' +
        ' 误差率   numeric(20,4)' +
        ')'
      ]);
  PSCon_Connection.ExecSQL(Sqlstr);

  for I := 1 to 22 do
    begin
      FiledName:='SCPB_水简称';
      case I of
        1:begin
         FiledName:='SCPB_水简称 AS YLMC ,GKJL_水_设定  AS 设定值 ,GKJL_水_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;

        2:begin
         FiledName:='SCPB_水2简称 AS YLMC ,GKJL_水2_设定  AS 设定值 ,GKJL_水2_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        3:begin
         FiledName:='SCPB_胶料简称 AS YLMC ,GKJL_胶料_设定  AS 设定值 ,GKJL_胶料_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        4:begin
         FiledName:='SCPB_胶料2简称 AS YLMC ,GKJL_胶料2_设定  AS 设定值 ,GKJL_胶料2_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        5:begin
         FiledName:='SCPB_胶料3简称 AS YLMC ,GKJL_胶料3_设定  AS 设定值 ,GKJL_胶料3_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        6:begin
         FiledName:='SCPB_掺料简称 AS YLMC ,GKJL_掺料_设定  AS 设定值 ,GKJL_掺料_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        7:begin
         FiledName:='SCPB_掺料2简称 AS YLMC ,GKJL_掺料2_设定  AS 设定值 ,GKJL_掺料2_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        8:begin
         FiledName:='SCPB_掺料3简称 AS YLMC ,GKJL_掺料3_设定  AS 设定值 ,GKJL_掺料3_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        9:begin
         FiledName:='SCPB_掺料4简称 AS YLMC ,GKJL_掺料4_设定  AS 设定值 ,GKJL_掺料4_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        10:begin
         FiledName:='SCPB_细骨简称 AS YLMC ,GKJL_细骨_设定  AS 设定值 ,GKJL_细骨_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        11:begin
         FiledName:='SCPB_细骨2简称 AS YLMC ,GKJL_细骨2_设定  AS 设定值 ,GKJL_细骨2_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        12:begin
         FiledName:='SCPB_细骨3简称 AS YLMC ,GKJL_细骨3_设定  AS 设定值 ,GKJL_细骨3_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        13:begin
         FiledName:='SCPB_粗骨简称 AS YLMC ,GKJL_粗骨_设定  AS 设定值 ,GKJL_粗骨_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        14:begin
         FiledName:='SCPB_粗骨2简称 AS YLMC ,GKJL_粗骨2_设定  AS 设定值 ,GKJL_粗骨2_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        15:begin
         FiledName:='SCPB_粗骨3简称 AS YLMC ,GKJL_粗骨3_设定  AS 设定值 ,GKJL_粗骨3_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        16:begin
         FiledName:='SCPB_外剂简称 AS YLMC ,GKJL_外剂_设定  AS 设定值 ,GKJL_外剂_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        17:begin
         FiledName:='SCPB_外剂2简称 AS YLMC ,GKJL_外剂2_设定  AS 设定值 ,GKJL_外剂2_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        18:begin
         FiledName:='SCPB_外剂3简称 AS YLMC ,GKJL_外剂3_设定  AS 设定值 ,GKJL_外剂3_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        19:begin
         FiledName:='SCPB_外剂4简称 AS YLMC ,GKJL_外剂4_设定  AS 设定值 ,GKJL_外剂4_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        20:begin
         FiledName:='SCPB_外剂5简称 AS YLMC ,GKJL_外剂5_设定  AS 设定值 ,GKJL_外剂5_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        21:begin
         FiledName:='SCPB_其他简称 AS YLMC ,GKJL_其他_设定  AS 设定值 ,GKJL_其他_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
        22:begin
         FiledName:='SCPB_其他2简称 AS YLMC ,GKJL_其他2_设定  AS 设定值 ,GKJL_其他2_计量 as 计量值';
         Sqlstr :=GetSql(FiledName,Wr,WrRQ);
         UpdateGKJLSum(Sqlstr);
        end;
      end;
    end;

   Sqlstr :=CombinQuerySql('*','#GKJLSum');
   PSCon_Query.Close;
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Add('execute PRO_CiGongKSC :Sql');
   PSCon_Query.ParamByName('Sql').Value :=Sqlstr;
   PSCon_Query.Active :=True;
//  Result := PSCon_Query;

   Result := GetAppDataToJson( PSCon_Query.RecordCount,
     AppDataToJson(Total_Query,Total_Query.RecordCount),
     AppDataToJson(FDQuery1,FDQuery1.RecordCount),
     AppDataToJson(PSCon_Query,PSCon_Query.RecordCount)) ;

 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GetSumShengCXH(FormTm, ToTM, FZGS_ID, Search, SCMS,
  GCKH_MC: string): string;
var
  Wr,SCWr,WrRQ, Sqlstr, Ord: string;
  FiledName:string;
  I:Integer;
begin
 try

   if (Search<>'') then
   begin
    if (pos('C',UpperCase(Search))>0)or (pos('M',UpperCase(Search))>0) then
    begin
     Wr :=Format('规格标号 = ''%s''',[Search]);
     SCWr :=Format('规格标号 like ''%%%s%%''',[Search]);
    end else
    begin
     Wr :=Format('工程名称 like ''%%%s%%'' '+
     'or 施工单位 like ''%%%s%%'' or 部位名称 like ''%%%s%%'' '+
     'or 任务编号 like ''%%%s%%''',[Search,Search,Search,Search]);
     SCWr :=CombinAndSql(Wr,SCWr);
    end;
   end;
   if SCMS<>'' then
   begin
      Wr :=CombinAndSql(Wr,Format('生产模式=''%s''',[SCMS]));
      SCWr :=CombinAndSql(Wr,Format('生产模式=''%s''',[SCMS]));
   end;
   if GCKH_MC<>'' then
    begin
      Wr :=CombinAndSql(Wr,Format('工程名称=''%s''',[GCKH_MC]));
      SCWr :=CombinAndSql(Wr,Format('工程名称=''%s''',[GCKH_MC]));
   end;


 if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
  begin
     Wr := CombinAndSql(Wr,Format('GKSC_FZGS=''%s''',[FZGS_ID]));
     SCWr :=CombinAndSql(SCWr,Format('GKSC_FZGS=''%s''',[FZGS_ID]));
  end;

   Total_Query.SQL.Clear;
   Total_Query.SQL.Text := CombinQuerySql(
    'sum(isnull(生产数量,0)) as FTYS_YSSL,Count(*) 累计车次',
    'CiGongKongSC_intf with(Nolock)',
     CombinAndSql(SCWr,FromToWhereSQL(FormTm,
        DateTimeToStr(StrToDateTime(ToTM)-1/60/24), 'SCTM')));
   Total_Query.Open();

  if (FormTm = ToTM)  then
    WrRQ := CombinAndSql(WrRQ, Format('convert(char(10), GKJL_生产时间, 20)=''%s''', [FormTm]))
  else
    WrRQ := CombinAndSql(WrRQ, FromToWhereSQL(FormTm,
         DateTimeToStr(StrToDateTime(ToTM)-1/60/24) , 'GKJL_生产时间'));

   Sqlstr :=  Format('if exists (%s %s', [
      'select * from tempdb.dbo.sysobjects '+
      'where id = object_id(N''tempdb..#ToTalGKJLSum'') and type=''U'') drop table #ToTalGKJLSum',
       'create table #ToTalGKJLSum '+   // 创建临时表#Tmp
        '( '+
        ' YLMC   varchar(100) not null,'+//创建列ID,并且每次新增一条记录就会加1
        ' 设定值  numeric(20,4), '+
        ' 计量值   numeric(20,4), ' +
        ' 误差值   numeric(20,4), ' +
        ' 误差率   numeric(20,4)' +
        ')'
      ]);
  PSCon_Connection.ExecSQL(Sqlstr);

  for I := 1 to 22 do
    begin
      FiledName:='SCPB_水简称';
      case I of
        1:begin
         FiledName:='SCPB_水简称 AS YLMC ,GKJL_水_设定  AS 设定值 ,GKJL_水_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;

        2:begin
         FiledName:='SCPB_水2简称 AS YLMC ,GKJL_水2_设定  AS 设定值 ,GKJL_水2_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        3:begin
         FiledName:='SCPB_胶料简称 AS YLMC ,GKJL_胶料_设定  AS 设定值 ,GKJL_胶料_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        4:begin
         FiledName:='SCPB_胶料2简称 AS YLMC ,GKJL_胶料2_设定  AS 设定值 ,GKJL_胶料2_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        5:begin
         FiledName:='SCPB_胶料3简称 AS YLMC ,GKJL_胶料3_设定  AS 设定值 ,GKJL_胶料3_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        6:begin
         FiledName:='SCPB_掺料简称 AS YLMC ,GKJL_掺料_设定  AS 设定值 ,GKJL_掺料_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        7:begin
         FiledName:='SCPB_掺料2简称 AS YLMC ,GKJL_掺料2_设定  AS 设定值 ,GKJL_掺料2_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        8:begin
         FiledName:='SCPB_掺料3简称 AS YLMC ,GKJL_掺料3_设定  AS 设定值 ,GKJL_掺料3_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        9:begin
         FiledName:='SCPB_掺料4简称 AS YLMC ,GKJL_掺料4_设定  AS 设定值 ,GKJL_掺料4_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        10:begin
         FiledName:='SCPB_细骨简称 AS YLMC ,GKJL_细骨_设定  AS 设定值 ,GKJL_细骨_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        11:begin
         FiledName:='SCPB_细骨2简称 AS YLMC ,GKJL_细骨2_设定  AS 设定值 ,GKJL_细骨2_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        12:begin
         FiledName:='SCPB_细骨3简称 AS YLMC ,GKJL_细骨3_设定  AS 设定值 ,GKJL_细骨3_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        13:begin
         FiledName:='SCPB_粗骨简称 AS YLMC ,GKJL_粗骨_设定  AS 设定值 ,GKJL_粗骨_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        14:begin
         FiledName:='SCPB_粗骨2简称 AS YLMC ,GKJL_粗骨2_设定  AS 设定值 ,GKJL_粗骨2_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        15:begin
         FiledName:='SCPB_粗骨3简称 AS YLMC ,GKJL_粗骨3_设定  AS 设定值 ,GKJL_粗骨3_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        16:begin
         FiledName:='SCPB_外剂简称 AS YLMC ,GKJL_外剂_设定  AS 设定值 ,GKJL_外剂_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        17:begin
         FiledName:='SCPB_外剂2简称 AS YLMC ,GKJL_外剂2_设定  AS 设定值 ,GKJL_外剂2_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        18:begin
         FiledName:='SCPB_外剂3简称 AS YLMC ,GKJL_外剂3_设定  AS 设定值 ,GKJL_外剂3_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        19:begin
         FiledName:='SCPB_外剂4简称 AS YLMC ,GKJL_外剂4_设定  AS 设定值 ,GKJL_外剂4_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        20:begin
         FiledName:='SCPB_外剂5简称 AS YLMC ,GKJL_外剂5_设定  AS 设定值 ,GKJL_外剂5_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        21:begin
         FiledName:='SCPB_其他简称 AS YLMC ,GKJL_其他_设定  AS 设定值 ,GKJL_其他_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
        22:begin
         FiledName:='SCPB_其他2简称 AS YLMC ,GKJL_其他2_设定  AS 设定值 ,GKJL_其他2_计量 as 计量值';
         Sqlstr :=GetToTalSql(FiledName,Wr,WrRQ);
         UpdateToTalGKJLSum(Sqlstr);
        end;
      end;
    end;

   Sqlstr :=CombinQuerySql('*','#ToTalGKJLSum');
   PSCon_Query.Close;
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Add('execute PRO_CiGongKSC :Sql');
   PSCon_Query.ParamByName('Sql').Value :=Sqlstr;
   PSCon_Query.Active :=True;

   Result := GetAppDataToJson( PSCon_Query.RecordCount,
     AppDataToJson(Total_Query,Total_Query.RecordCount),
     AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));

 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;

end;

function TPSConDataModule.GetToTalSql(FiledName, Wr, WrRQ: string): string;
var
  Sqlstr:string;
begin
   if Wr<>'' then
     Wr := 'where isnull(设定值,0)>0 and '+Wr
     else
     Wr := 'where isnull(设定值,0)>0 ';

   Sqlstr :='select YLMC,sum(设定值)设定值,sum(计量值)计量值,sum(设定值)-sum(计量值) 误差值'+
     ',case when isnull(sum(计量值),0)-isnull(sum(设定值),0)=0 then 0  '+
     '     when isnull(sum(设定值),0)=0 then 0 when isnull(sum(计量值),0)-isnull(sum(设定值),0)<>0 '+
       '     then convert(decimal(18,2),(isnull(sum(计量值),0)-isnull(sum(设定值),0))/isnull(sum(设定值),0)*100)'+
     ' end  误差率 '+
      'from ( '+
      ' SELECT UNIT,GKJL_FID,GKJL_PNO,GKJL_生产盘量,GKJL_生产日期,GKJL_生产时间,'+
        FiledName+' FROM ciGongKongJL with(nolock) where '+WrRQ+
     ')GKJL left join ciGongKongSC_Intf GKSC with(nolock) on '+
       ' GKJL.GKJL_FID=GKSC.SCID and GKJL.UNIT=GKSC.UNIT and GKJL.GKJL_生产日期=GKSC.SCRQ '+
       ' '+Wr+ ' group by YLMC';

   Result :=Sqlstr;

end;

function TPSConDataModule.getUseradmin(GCKH_ID: string): Variant;
var
  V:Variant;
  Sql:string;
  VarArr: TStrArr;
  I,L:Integer;
begin
 try
   L :=Db.QueryValue('Count(*)',TConfigFile.GetAppDb+'..PushAppAdmin with(nolock)','isnull(owner,0)=1');
   SetLength(VarArr,L);

   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := CombinQuerySql('ID',TConfigFile.GetAppDb+'..PushAppAdmin with(nolock)',
   'isnull(owner,0)=1','order by ID');
   PSCon_Query.Open();

  PSCon_Query.First;
   I := 0;
  while not PSCon_Query.Eof do
  begin
    VarArr[i] := PSCon_Query.FieldByName('ID').AsString ;  //UserName
    I := I+1;
    PSCon_Query.Next;
  end;

  Result := VarArr;
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.Get_Daily_Monthly_Annual(FromTM, ToTM, SCMS,FZGS_ID: string;
  IsD, IsM, IsA: Boolean): String;
  Var
  Wr:string;
begin
   Wr :=CombinAndSql(Wr,Format('SCTM between ''%s'' and ''%s'' ',
      [FromTM,ToTM]));
   if SCMS<>'' then
      Wr :=CombinAndSql(Wr,Format('生产模式=''%s''',[SCMS]));
   if (FZGS_ID<>'') and (FZGS_ID<>'{11111111-1111-1111-1111-111111111111}') then
      Wr := CombinAndSql(Wr,Format('GKSC_FZGS=''%s''',[FZGS_ID]));
  try
   if IsD then
    PSCon_Query.Open(CombinQuerySql(
     Format('''%s'' Daily_FromTM,''%s'' Daily_ToTM,',[FromTM,ToTM])+
     ' sum(isnull(生产数量,0)) as Daily_YSSL,Count(*) Daily_车次',
      'CiGongKongSC_intf with(Nolock)',Wr ));
   if IsM then
    PSCon_Query.Open(CombinQuerySql(Format('''%s'' Monthly_FromTM,''%s'' Monthly_ToTM,',[FromTM,ToTM])+
      'sum(isnull(生产数量,0)) as Monthly_YSSL,Count(*) Monthly_车次',
      'CiGongKongSC_intf with(Nolock)',Wr ));
   if IsA then
    PSCon_Query.Open(CombinQuerySql(Format('''%s'' Annual_FromTM,''%s'' Annual_ToTM,',[FromTM,ToTM])+
     'sum(isnull(生产数量,0)) as Annual_YSSL,Count(*) Annual_车次',
      'CiGongKongSC_intf with(Nolock)',Wr ));
  except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
  end;
   Result := GetAppDataToJson( PSCon_Query.RecordCount,
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount)) ;
end;

function TPSConDataModule.Get_YLPZ_MC(YLPZ_LX: Integer): string;
 var
  Wr,Sqlstr:string;
begin
  if YLPZ_LX> 0 then
       Wr := CombinAndSql(Wr,Format('YLPZ_原料类型=%d',[YLPZ_LX]));
   Sqlstr :=  Format('if exists (%s %s', [
      'select * from tempdb.dbo.sysobjects '+
      'where id = object_id(N''tempdb..#YLPZ_MC'') and type=''U'')'+
      ' drop table #YLPZ_MC',
      ' create table #YLPZ_MC '+// 创建临时表#Tmp
        '( '+
        ' YLPZ_名称 VarChar(60)'+
        ')'
      ]);
    PSCon_Connection.ExecSQL(Sqlstr);

     PSCon_Connection.ExecSQL(
        CombinInsertSql('#YLPZ_MC','YLPZ_名称',
        Format('''%s''',[''])));

    FDQuery1.Open(CombinQuerySql('YLPZ_名称','cmYuanLiaoPZ with(nolock)',Wr));
     if not FDQuery1.IsEmpty then
     begin
      FDQuery1.First;
      while not FDQuery1.Eof do
      begin
       begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#YLPZ_MC','YLPZ_名称',
        Format('''%s''',[FDQuery1.FieldByName('YLPZ_名称').AsString])));
       end;
       FDQuery1.Next;
      end;
     end;
   PSCon_Query.Open(CombinQuerySql('*','#YLPZ_MC'));
   Result := GetAppDataToJson(DB.QueryValue('count(*) rows',
    '#YLPZ_MC with(nolock)'),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount)) ;
end;

function TPSConDataModule.Gongkongsc(Search, FromTM, ToTM, UserFID,GCKH_MC,SCMS: string;
  PageNo,JZ: Integer):String;
  var
   Wr,Rwr:string;
begin
 try
    if (FromTM = ToTM) then
    Wr := Format('convert(char(10), SCTM, 20)=''%s''', [FromTM])
  else
    Wr := Format('SCTM between ''%s'' and ''%s'' ',[FromTM,ToTM]);

    if (UserFID <> '{11111111-1111-1111-1111-111111111111}' ) then
			Wr := CombinAndSql( Wr, Format('GKSC_FZGS=''%s''',[UserFID]));

		if (Search <>'') then
    begin
     if (Pos('C' ,UpperCase(Search))>0)or (Pos('M' ,UpperCase(Search))>0) then
       Search :=Format( '规格标号 like ''%%%s%%''',[Search])
     else
			Search := Format( '施工单位 like ''%%%s%%'' '+
      'or 任务编号 like ''%%%s%%'' or 部位名称 like ''%%%s%%''',
       [Search,Search,Search]);
			Wr := CombinAndSql(Wr,Search);
    end;

    if GCKH_MC<>'' then
    begin
     Wr := CombinAndSql(Wr,Format( '工程名称 like  ''%%%s%%''',[GCKH_MC]));
    end;
    if JZ>0 then
       Wr := CombinAndSql(Wr,Format( 'Unit =  %d',[JZ]));
    if SCMS<>'' then
       Wr := CombinAndSql(Wr,Format('生产模式=''%s''',[SCMS]));

    Total_Query.SQL.Clear;
    Total_Query.SQL.Text :=CombinQuerySql('Sum(生产数量)SCSL,Count(*)LJCL',
    'ciGongKongSC_Intf with(nolock)',Wr);
    Total_Query.Open();

    PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +' * ',
    '('+
      CombinQuerySql('row_number()over(order by 生产编号)rownumber,'+
      'UNIT,SCRQ,SCTM,SCID,PBID,客户编号 KHBH,生产编号 SCBH,任务编号 RWBH'+
      ',合同编号 HTBH,工程名称 GCMC,施工单位 SGDW,部位名称 BWMC '+
      ',规格标号 GGBH,坍落稠度 TLD,运输车辆 YSCH,运输司机 SJXM'+
      ',生产数量 SCSL,盘次总数 PCZL,配比编号 PBBH,附加内容 FJNR'+
      ',ISN,IUP','ciGongKongSC_Intf with(nolock)',Wr)+
      ' )GKSC',
     Format('rownumber>%d',[Top*(PageNo - 1)]));
   PSCon_Query.Open;

   Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
    'ciGongKongSC_Intf with(nolock)',Wr),
    AppDataToJson(Total_Query,Total_Query.RecordCount),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount)) ;
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.GongkongscZ(Search, FromTM, ToTM, UserFID,
  GCKH_MC: string; PageNo, JZ: Integer): String;
  var
   Wr,Rwr:string;
begin
 try
    if (FromTM = ToTM) then
    Wr := Format('convert(char(10), SCTM, 20)=''%s''', [FromTM])
  else
    Wr := Format('SCTM between ''%s'' and ''%s'' ',[FromTM,ToTM]);

    if (UserFID <> '{11111111-1111-1111-1111-111111111111}' ) then
			Wr := CombinAndSql( Wr, Format('GKSC_FZGS=''%s''',[UserFID]));

		if (Search <>'') then
    begin
			Search := Format( '施工单位 like ''%%%s%%'' '+
      'or 任务编号 like ''%%%s%%'' or 部位名称 like ''%%%s%%''',
       [Search,Search,Search]);
			Wr := CombinAndSql(Wr,Search);
    end;
    if GCKH_MC<>'' then
     Wr := CombinAndSql(Wr,Format( '工程名称 like  ''%%%s%%''',[GCKH_MC]));
    if JZ>0 then
       Wr := CombinAndSql(Wr,Format( 'Unit =  %d',[JZ]));

    Total_Query.SQL.Clear;
    Total_Query.SQL.Text :=CombinQuerySql('Sum(生产数量)SCSL,Count(*)LJCL',
    'ciGongKongSC_Intf with(nolock)',Wr);
    Total_Query.Open();

    PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +' * ',
    '('+
      CombinQuerySql('row_number()over(order by 工程名称)rownumber,'+
      '工程名称 GCMC,施工单位 SGDW',
      'ciGongKongSC_Intf with(nolock)',Wr,'Group by 工程名称,施工单位')+
      ' )GKSC',
     Format('rownumber>%d',[Top*(PageNo - 1)]));
   PSCon_Query.Open;

   Result := GetAppDataToJson(DB.QueryValue('count(*) rows',
    'ciGongKongSC_Intf with(nolock)',Wr,'Group by 工程名称,施工单位'),
    AppDataToJson(Total_Query,Total_Query.RecordCount),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;

end;

function TPSConDataModule.InsertSCRW(GCKH_ID, GGBH_ID, JHSJ, JZBW, ISBJ, SJSL,
  TLD, GGBH, FJBH, JHSL, SCRW_LXR, SCRW_LXRFS: string; GGBH_LX,
  IsBS: Integer):String;
  var
  SCRW_ID,SCRW_BH :string;
begin
  SCRW_ID := Db.GenerateGUID;
  SCRW_BH := GetSCRWBH(SCRW_ID,GetStrTodateTime(FormatDateTime('YYYY-MM-DD HH:MM:SS',Now)));
			//补全砂浆数量
   if ISBJ='0' then
       SJSL := '0';
 try
  PSCon_Connection.StartTransaction;
   with PSCon_Command do
   begin
     CommandText.Text := CombinInsertSql('cpShengChanRW',
     'SCRW_ID , GCKH_ID , GGBH_ID , SCRW_MTM , SCRW_DEL , SCRW_编号 , SCRW_日期 , SCRW_计划时间 ,'+
     'SCRW_部位名称 , SCRW_品种类型_, SCRW_规格标号_ , SCRW_附加标号 , SCRW_坍落稠度 ,'+
     ' SCRW_计划数量 , SCRW_要求泵浆 ,SCRW_泵浆数量 , SCRW_泵送类型 , SCRW_要求资料 , '+
     'SCRW_联系人 , SCRW_联系方式,SCRW_ISApp,SCRW_状态,SCRW_开工时间',

     ':SCRW_ID,:GCKH_ID,:GGBH_ID,:SCRW_MTM,:SCRW_DEL,:SCRW_编号,:SCRW_日期,:SCRW_计划时间,'+
     ':SCRW_部位名称,:SCRW_品种类型_,:SCRW_规格标号_,:SCRW_附加标号,:SCRW_坍落稠度,'+
     ':SCRW_计划数量,:SCRW_要求泵浆,:SCRW_泵浆数量, :SCRW_泵送类型,:SCRW_要求资料, '+
     ':SCRW_联系人,:SCRW_联系方式,:SCRW_ISApp,:SCRW_状态,:SCRW_开工时间'
      );
     Params.ParamByName('SCRW_ID').AsString :=SCRW_ID ;
     Params.ParamValues['GCKH_ID'] := GCKH_ID;
     Params.ParamValues['GGBH_ID'] := GGBH_ID ;
     Params.ParamValues['SCRW_MTM'] := FormatDateTime('YYYY-MM-DD HH:MM:SS',Date+Time);
     Params.ParamValues['SCRW_DEL'] := 0;
     Params.ParamValues['SCRW_编号']:= SCRW_BH;
     Params.ParamValues['SCRW_日期'] := FormatDateTime('YYYY-MM-DD',Date);
     Params.ParamValues['SCRW_计划时间'] :=JHSJ ;

     Params.ParamValues['SCRW_部位名称'] := JZBW;
     Params.ParamValues['SCRW_品种类型_'] := GGBH_LX ;
     Params.ParamValues['SCRW_规格标号_'] := GGBH ;
     Params.ParamValues['SCRW_附加标号'] := FJBH ;
     Params.ParamValues['SCRW_坍落稠度'] := TLD;
     Params.ParamValues['SCRW_计划数量'] := JHSL;
     Params.ParamValues['SCRW_要求泵浆'] :=ISBJ ;
     Params.ParamValues['SCRW_泵浆数量'] := SJSL;
     Params.ParamValues['SCRW_泵送类型'] := 0 ;
     Params.ParamValues['SCRW_要求资料'] := 1 ;
     Params.ParamValues['SCRW_联系人'] := SCRW_LXR ;
     Params.ParamValues['SCRW_联系方式'] := SCRW_LXRFS;
     Params.ParamValues['SCRW_ISApp'] := 1;
     Params.ParamValues['SCRW_状态'] := 0;
     Params.ParamValues['SCRW_泵送类型']:= IsBS;
     Params.ParamValues['SCRW_开工时间'] := FormatDateTime('YYYY-MM-DD',Date);

     Execute();
   end;
   PSCon_Connection.Commit;
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      PSCon_Connection.Rollback;
    end;
 end;
 //二次更新SCRW_编号
 try
  SCRW_BH := GetSCRWBH(SCRW_ID,GetStrTodateTime(FormatDateTime('YYYY-MM-DD HH:MM:SS',Date+Time)));
  PSCon_Connection.StartTransaction;
   with PSCon_Command do
   begin
     CommandText.Text := CombinUpdateSql('cpShengChanRW',
     'SCRW_编号=:SCRW_编号','SCRW_ID=:SCRW_ID');
     Params.ParamByName('SCRW_ID').AsString :=SCRW_ID;
     Params.ParamValues['SCRW_编号']:= SCRW_BH;
     Execute();
   end;
    PSCon_Connection.Commit;
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      PSCon_Connection.Rollback;
    end;
 end;
   Result := GetAppDataToJson(DB.QueryValue('count(*) rows',
    'cpShengChanRW SCRW with(nolock) ',Format('SCRW_ID=''%s''',[SCRW_ID])),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
end;

procedure TPSConDataModule.posCreateGroup(GCKH_ID,groupname,maxusers,owner:string);
  var
  JSONObject:TJSONObject;
  LBody,RommID,error,Msg: string;
  LResp: IRESTResponse;
  FRESTClient:TRESTClient;
  Code:Integer;
begin
 try
   {$IFDEF SSL}  //SSL Web
     FRESTClient := TRESTClient.Create('https://www.pushcon.ltd',443);
   {$ELSE}
     TRESTClient.Create('http://127.0.0.1',TConfigFile.GetAPIPort);
   {$ENDIF}
  with FRESTClient do
  begin
     LBody :=Format('GCKH_ID=%s&G_ID=%s&groupname=%s&maxusers=%s&owner=%s',
      [GCKH_ID,GCKH_ID,groupname,maxusers,owner]);
    LResp := doPOST('/Easemob/NewGroup',[],LBody);
//     LBody :=Format('ID=%s&rows=%s&Page=%s&sort=%s&order=%s',
//      ['{1AE4C3A3-4EC2-4609-84E2-77C21C306E6B}','20','1','J_MTM','desc']);
//    LResp := doPOST('/Easemob/NewGroup',[],LBody);
  end;
 finally
   FRESTClient.Free;
 end;
end;

procedure TPSConDataModule.PSCon_ConnectionBeforeConnect(Sender: TObject);
begin
//  try
//   if not PSCon_Connection.Connected then
//     PSCon_Connection.Open;
//  except
//   on E: Exception do
//    begin
//      Log('!'+E.ToString);
//     raise;
//    end;
//  end;
end;

function TPSConDataModule.QuerySCRW_SCPB(RWZT,PBZT,ND,YF,KW: integer; KeyWd,UserFID,
  FromDt,TromDt, RWBH, GGBH, PBBH: string; PZLX,PageNo: Integer): string;
var
  Wr: string;
begin
  if (not UserFID.IsEmpty) and (UserFID <> '{11111111-1111-1111-1111-111111111111}') then
    Wr := CombinAndSql(Wr,Format('SCRW_FZGS=''%s''',[UserFID]));

  if PBZT<>1 then
  begin
    case RWZT of
      0: Wr := CombinAndSql(Wr, 'SCRW_状态 = 0');
      1: Wr := CombinAndSql(Wr, 'SCRW_状态 in (1,2)');
      2: Wr := CombinAndSql(Wr, 'SCRW_状态 in (3,4)');
    end;
  end else
   Wr :=  CombinAndSql(Wr, 'SCRW_状态 in (1,2)');

  if PBZT>=0 then
    Wr := CombinAndSql(Wr, Format('Isnull(SCPB_签发状态,0) =%d',[PBZT]));

  if ND > 0 then
  begin
    Wr := CombinAndSql(Wr, Format('year(SCRW_日期)=%d', [ND]));

    if YF > 0 then
      Wr := CombinAndSql(Wr, Format('month(SCRW_日期)=%d', [YF]));
  end else  // 仅非年度时附加开始日期条件
  begin
    if RWZT = 2 then
    begin
      if (FromDt<>'') and (TromDt<>'') then
       Wr := CombinAndSql(Wr, FromToWhereSQL(FromDt,TromDt,'SCRW_日期'))
      else
       Wr := CombinAndSql(Wr, FromToWhereSQL(FromDt,TromDt,'SCRW_日期'));
    end;
  end;
  //任务编号
  if RWBH<>'' then
    Wr :=CombinAndSql(Wr,Format('SCRW_编号 like ''%%%s%%''',[RWBH]));
  if KeyWd <> '' then
  begin
   Wr := CombinAndSql(Wr,
    Format('SCRW_编号 like ''%s%%'' or GCKH_编号 = ''%%%s%%'' or '+
    'GCKH_合同号 = ''%%%s%%'' or '+
    'SCRW_部位名称 like ''%%%s%%'' or GCKH_工程名称 like ''%%%s%%'' or '+
    'SCRW_分包单位 like ''%%%s%%'' or GCKH_施工单位 like ''%%%s%%'' or '+
    'SCPB_配比号 like ''%%%s%%'' or SCRW_规格标号_附加标号 like ''%%%s%%''',
    [KeyWd,KeyWd,KeyWd,KeyWd,KeyWd,KeyWd,KeyWd,KeyWd,KeyWd]));
  end;

  if GGBH<>'' then
    Wr := CombinAndSql(Wr, format('SCRW_规格标号_附加标号=''%s''',[GGBH]));
  if PBBH <>'' then
    Wr := CombinAndSql(Wr, format('SCPB_配比号=''%s''',[PBBH]));
  if PZLX>0 then
    Wr := CombinAndSql(Wr, format('SCRW_品种类型=%d',[PZLX]));

   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +' * ',
    '('+
    CombinQuerySql('row_number()over(order by GCKH_工程名称,SCRW_日期,SCRW_编号)rownumber,'+
    'SCRW_日期,SCRW.SCRW_ID,GCKH_工程名称,GCKH_施工单位,'+
   'IsNull(SCRW.SCRW_部位名称,'''')SCRW_部位名称,SCRW_规格标号_附加标号,'+
   'IsNull(SCRW_输送方式,'''')SCRW_输送方式,IsNull(GCKH_施工地点,'''')GCKH_施工地点,'+
   'SCRW_泵送类型名称,IsNull(SCRW_坍落稠度,'''')SCRW_坍落稠度,SCPB_签发状态,SCPB_审核,'+
   'SCPB_NOID,IsNull(SCPB_配比号,'''')SCPB_配比号',
   'cpShengChanRWView SCRW left join cpShengChanPB SCPB on '+
   'SCRW.SCRW_ID = SCPB.SCRW_ID and SCPB.SCPB_NOID = SCRW.SCRW_配比次数',
    Wr)+' )QuerySCRW_SCPB',
     Format('rownumber>%d',[Top*(PageNo - 1)]));
   PSCon_Query.Open;
  PSCon_Query.Last;
  Result := GetAppDataToJson(db.QueryValue('Count(*)Rows',
   'cpShengChanRWView SCRW left join cpShengChanPB SCPB on '+
   'SCRW.SCRW_ID = SCPB.SCRW_ID and SCPB.SCPB_NOID = SCRW.SCRW_配比次数',Wr),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
end;

function TPSConDataModule.SelectRBMX(FromTM, ToTM,
  GCKH_ID: string):String;
  var
   Wr,Rwr:string;
begin
    if (FromTM = ToTM) then
    Wr := Format('convert(char(10), FTYS_时间, 20)=''%s''', [FromTM])
  else
    Wr := Format('FTYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]);
   Wr := CombinAndSql(Wr,Format('SCRW.GCKH_ID=''%s''',[GCKH_ID]));
 try
   PSCon_Query.SQL.Clear;
      PSCon_Query.SQL.Text :=CombinQuerySql('*','('+
      CombinQuerySql('SCRW.SCRW_ID as SCRWID,max(FTYS_累计车次) FTYS_LJCC,'+
       'SUM(isnull(FTYS_生产数量,0))FTYS_SCSL,SUM(isnull(FTYS_运输数量,0))FTYS_YSSL,'+
       'sum(isnull(FTYS_商砼数量2,0))FTYS_Check,'+
       'sum(isnull(FTYS_退剩数量1,0))FTYS_Return,sum(isnull(FTYS_退剩数量2,0))FTYS_Surplus,'+
       'sum(case FTYS_运输内容 when 0 then case when (FTYS_商砼数量2-FTYS_生产数量)>=0 then '+
       '(FTYS_商砼数量2-FTYS_生产数量) when (FTYS_商砼数量2-FTYS_生产数量)<0 then 0 end  '+
       'when 1 then case when (FTYS_泵浆数量2-FTYS_生产数量)>=0 then '+
       '(FTYS_泵浆数量2-FTYS_生产数量) when (FTYS_泵浆数量2-FTYS_生产数量)<0 then 0 end end )FTTJ_LOSS',
             'cpFaTongYSView FTYS with(Nolock) '+
             'left join cpShengChanRW SCRW with(nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',Wr,
             'group by SCRW.SCRW_ID')
          +')FTYS left join ('+
           CombinQuerySql('SCRW_ID,SCRW_编号 SCRW_BH, '+
            'SCRW_输送方式 SCRW_JZFS,SCRW_部位名称 SCRW_BWMC,'+
            'SCRW_规格标号_+SCRW_附加标号 SCRW_TBH,SCRW_配比号 as SCRW_PBH,'+
            'sum(isnull(SCRW_计划数量,0)) SCRW_JHSL','cpShengChanRW with(Nolock)',
          Format('GCKH_ID=''%s''',[GCKH_ID]),
             'group by SCRW_ID,SCRW_编号,SCRW_输送方式,SCRW_部位名称,'+
             'SCRW_规格标号_+SCRW_附加标号,SCRW_配比号')
           +')SCRW  on FTYS.SCRWID=SCRW.SCRW_ID order by SCRW_BH');
   PSCon_Query.Open;

   Result := GetAppDataToJson(DB.QueryValue('count(*) rows',
    'cpShengChanRW SCRW with(nolock) '+
    'left join cpFaTongYSView FTYS on SCRW.SCRW_ID=FTYS.SCRW_ID',Wr,
     'group by SCRW.SCRW_ID,SCRW_编号,SCRW_输送方式,SCRW_部位名称,SCRW_规格标号_+SCRW_附加标号'),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.SelectSCRW(FromTM,ToTM, GCKH_ID,TBH: string;PageNo,ISSH:Integer):String;
var
  Wr:string;
begin
 try
  if ISSH >0 then
    Wr := Format('FTYS_回单审核>=2 and FTYS_审核时间 >=''%s'' and FTYS_审核时间<=''%s'' and GCKH_ID=''%s''',
    [FromTM,ToTM,GCKH_ID])
   else
   Wr := Format('FTYS_时间 >=''%s'' and FTYS_时间<=''%s'' and GCKH_ID=''%s''',
    [FromTM,ToTM,GCKH_ID]);
  if TBH<>'' then
    Wr :=CombinAndSql(Wr,Format('SCRW_规格标号_+SCRW_附加标号 like ''%%%s%%''',[TBH]));

  Total_Query.SQL.Clear;
  Total_Query.SQL.Text :=CombinQuerySql('sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,'+
       'sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,Count(*) 累计车次',
       'cpFaTongYS FTYS with(Nolock) '+
       'left join cpShengChanRW SCRW with(nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',
       CombinAndSql(Wr,'FTYS_Del=0 and FTYS_签发审核=1'));
    Total_Query.Open();

   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text :=CombinQuerySql('top '+ intToStr(Top) +' * ',
   '('+
     CombinQuerySql('row_number()over(order by SCRW.SCRW_ID)rownumber,'+
       'SCRW.SCRW_ID as SCRWID,Count(*) FTYS_LJCC,'+
       'SUM(isnull(FTYS_生产数量,0))FTYS_SCSL,SUM(isnull(FTYS_运输数量,0))FTYS_YSSL',
       'cpFaTongYS FTYS with(Nolock) '+
       'left join cpShengChanRW SCRW with(nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',Wr,
       'group by SCRW.SCRW_ID')
    +')FTYS left join ('+
     CombinQuerySql('row_number()over(order by SCRW.SCRW_ID)rownumber,'+
      'SCRW.SCRW_ID,SCRW_编号 SCRW_BH, '+
      'SCRW_输送方式 SCRW_JZFS,SCRW_部位名称 SCRW_BWMC,'+
      'SCRW_规格标号_+SCRW_附加标号 SCRW_TBH,SCPB_配比号 as SCRW_PBH,'+
      'sum(isnull(SCRW_计划数量,0)) SCRW_JHSL',
      'cpShengChanRW SCRW with(Nolock) '+
      'left join cpShengChanPB SCPB with(Nolock) '+
      'on SCRW.SCRW_ID=SCPB.SCRW_ID and SCRW.SCRW_配比次数=SCPB.SCPB_NOID',
    Format('GCKH_ID=''%s''',[GCKH_ID]),
       'group by SCRW.SCRW_ID,SCRW_编号,SCRW_输送方式,SCRW_部位名称,'+
       'SCRW_规格标号_+SCRW_附加标号,SCPB_配比号')
     +')SCRW  on FTYS.SCRWID=SCRW.SCRW_ID',
     Format('FTYS.rownumber>%d',[Top*(PageNo - 1)]));

   PSCon_Query.Open;


   Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
   '('+
    CombinQuerySql('count(*) rows',
     'cpShengChanRW SCRW with(nolock)'+
    ' left join  cpFaTongYS FTYS on SCRW.SCRW_ID=FTYS.SCRW_ID',Wr,
    'group by SCRW.SCRW_ID,SCRW_编号,SCRW_输送方式,'+
       'SCRW_部位名称,SCRW_规格标号_+SCRW_附加标号')+
    ')A' ),
    AppDataToJson(Total_Query,Total_Query.RecordCount),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.SetCreateGroup: string;
var
  GCKH_ID,GCMC,owner:string;
begin
  PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := CombinQuerySql('*',format('%s..PushAppAdmin with(nolock)',
     [TConfigFile.GetAppDb]),'[owner]=1');
  PSCon_Query.Open();
  owner :=PSCon_Query.FieldByName('ID').AsString;
  try
   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text :=CombinQuerySql('GCKH_ID,GCKH_工程名称','cpGongChengKH with(nolock)',
    format('isnull(GCKH_工程状态,0)=0 and GCKH_ID<>''{00000000-0000-0000-0000-000000000000}'''+
    ' and GCKH_ID not in (select G_ID GCKH_ID from %s..createGroup)',[TConfigFile.GetAppDb]));
   PSCon_Query.Open();

   PSCon_Query.First;
   while not PSCon_Query.Eof do
   begin
     GCKH_ID:= PSCon_Query.FieldByName('GCKH_ID').AsString;
     GCMC := PSCon_Query.FieldByName('GCKH_工程名称').AsString;
     CreateAppGroup(GCMC,owner,GCKH_ID);
    PSCon_Query.Next;
   end;

  except
    on e : Exception do
    begin
      LogI('!'+e.Message);
      Raise;
    end;
  end;
    Result :=GCKH_ID;
end;

function TPSConDataModule.Shengchanrb(Search, FromTM, ToTM, UserFID: string;
  ISGC, PageNo: Integer):String;
  var
   Wr,Rwr:string;
begin
   if (FromTM = ToTM) then
     ToTM :=DateTimeToStr(date+time);// GetStrTodateTime(ToTM)+1/24);

    Wr := Format('FTYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]);
   if (ISGC = 1) then
			Wr := CombinAndSql(Wr,Format('SCRW.GCKH_ID=''%s''',[UserFID]))
		 else  begin
			if (UserFID <>'{11111111-1111-1111-1111-111111111111}')then
				Wr := CombinAndSql (Wr,Format('FTYS.FTYS_FZGS=''%s''',[UserFID]));
		end;

		if (Search <>'') then
    begin
			Search := Format( 'GCKH_工程名称 like  ''%%%s%%''  or GCKH_工程名称 ',
       [Search,Search]);
			Wr := CombinAndSql(Wr,Search);
    end;

  try
    PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +
    ' rownumber,GCKH_ID,GCKH_GCMC,GCKH_SGDW,SCRW_JHSL,FTYS_YSSL,FTYS_SCSL,FTYS_Check,'+
    'FTYS_Return,FTYS_Surplus,FTTJ_LOSS',
    '('+
      CombinQuerySql('row_number()over(order by GCKH_工程名称)rownumber,'+
      'GCKH.GCKH_ID,GCKH_工程名称 GCKH_GCMC,GCKH_施工单位 GCKH_SGDW,'+
      'SUM(isnull(FTYS_运输数量,0))FTYS_YSSL,'+
      'SUM(isnull(FTYS_生产数量,0))FTYS_SCSL,sum(isnull(FTYS_商砼数量2,0))FTYS_Check,'+
      'sum(isnull(FTYS_退剩数量1,0))FTYS_Return,sum(isnull(FTYS_退剩数量2,0))FTYS_Surplus,'+
      'sum(isnull(case FTYS_运输内容 when 0 then case when (FTYS_商砼数量2-FTYS_生产数量)>=0 '+
      'then (FTYS_商砼数量2-FTYS_生产数量) when (FTYS_商砼数量2-FTYS_生产数量)<0 then 0 end  when 1 then '+
      'case when (FTYS_泵浆数量2-FTYS_生产数量)>=0 then (FTYS_泵浆数量2-FTYS_生产数量) when '+
      '(FTYS_泵浆数量2-FTYS_生产数量)<0 then 0 end end,0))FTTJ_LOSS',
      'cpFaTongYSView FTYS with(nolock) '+
      ' left join cpShengChanRW SCRW on FTYS.SCRW_ID=SCRW.SCRW_ID'+
      ' left join cpGongChengKH GCKH on SCRW.GCKH_ID=GCKH.GCKH_ID',Wr,
      'group by GCKH.GCKH_ID,GCKH_工程名称,GCKH_施工单位')+
      ' )GCKH '+
      'left join ('+CombinQuerySql('sum(isnull(SCRW_计划数量,0)) as SCRW_JHSL,'+
      'GCKH_ID as ID',
      'cpShengChanRW','SCRW_ID in (select SCRW.SCRW_ID from  CPFatongYS FTYS with(nolock) ' +
      'left join cpShengChanRW SCRW with(nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID where '+Wr+
      ')group  by GCKH_ID')+') CC on GCKH.GCKH_ID= CC.ID',


     Format('rownumber>%d',[Top*(PageNo - 1)]),'order by rownumber');
    // LogI(PSCon_Query.SQL.Text);
   PSCon_Query.Open;

   Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
   '('+
    CombinQuerySql('count(*) rows',
     'cpFaTongYS FTYS with(nolock)'+
    ' left join cpShengChanRW SCRW on FTYS.SCRW_ID=SCRW.SCRW_ID'+
    ' left join cpGongChengKH GCKH on SCRW.GCKH_ID=GCKH.GCKH_ID',Wr,
    'group by GCKH.GCKH_ID,GCKH_工程名称,GCKH_施工单位')+
    ')A' ),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.ShengChanRenWu(Search, FromTM, ToTM, UserFID,GCKH_MC: string;
  PageNo, RWZT, ISGC: Integer):String;
  var
   Wr:string;
begin
 try
    Wr :='SCRW_ID<>''{00000000-0000-0000-0000-000000000000}''';
   if FromTM = ToTM then
    ToTM :=DateTimeToStr(GetStrTodateTime(ToTM)+1/24);

		if (ISGC = 1) then
			Wr := CombinAndSql (Wr,Format('GCKH_ID=''%s''',[UserFID]))
		 else begin
			if (UserFID <> '{11111111-1111-1111-1111-111111111111}') then
				Wr := CombinAndSql(Wr,Format('SCRW_FZGS=''%s''',[UserFID]));
     end;

		if (Search <>'') then
    begin
//			Search := Format( 'SCRW_编号 like  ''%%%s%%''  or GCKH_施工单位 like ''%%%s%%'' '+
//      'or GCKH_工程名称 like ''%%%s%%'' or SCRW_部位名称 like ''%%%s%%'' '+
//      'or SCRW_规格标号_附加标号 like ''%%%s%%''',
//       [Search,Search,Search,Search,Search]);
			Search := Format('GCKH_施工单位 like ''%%%s%%'' '+
             'or GCKH_工程名称 like ''%%%s%%'' ',[Search,Search]);
			Wr := CombinAndSql(Wr,Search);
    end;
    if GCKH_MC<>'' then
      Wr := CombinAndSql(Wr,Format('GCKH_工程名称 like ''%%%s%%''',[GCKH_MC]));

   	// case $RWZT
		case (RWZT) of
       0 : Wr :=CombinAndSql(Wr,Format('SCRW_日期 between ''%s'' and ''%s'' and SCRW_状态=%d',[FromTM,ToTM,RWZT]));
		   1 : Wr :=CombinAndSql(Wr,Format('SCRW_日期 between ''%s'' and ''%s'' and SCRW_状态=%d',[FromTM,ToTM,RWZT]));
		   2 : Wr :=CombinAndSql(Wr,Format('SCRW_日期 between ''%s'' and ''%s'' and SCRW_状态=%d',[FromTM,ToTM,RWZT]));
		   3 : Wr :=CombinAndSql(Wr,Format('SCRW_日期 between ''%s'' and ''%s'' and SCRW_状态=%d',[FromTM,ToTM,RWZT]));
	  end;

   FDQuery1.SQL.Clear;
   FDQuery1.SQL.Text := CombinQuerySql('GCKH_工程名称 GCKH_MC','cpGongChengKH with(Nolock)',
   Format('GCKH_ID in (%s)',[CombinQuerySql('GCKH_ID','cpShengChanRW with(Nolock)',
   Format('SCRW_日期 between ''%s'' and ''%s''',[FromTM,ToTM]))]));
   FDQuery1.Open();

   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +' * ',
    '('+
//      CombinQuerySql('row_number()over(order by SCRW_ISN)rownumber,'+
//       'SCRW_ID,GCKH_工程名称 GCKH_MC,GCKH_施工单位 GCKH_SGDW,SCRW_部位名称 SCRW_BWMC,'+
//       'SCRW_编号 SCRW_BH,SCRW_输送方式 SCRW_JZFS,SCRW_规格标号_附加标号 SCRW_TBH,'+
//       'SCRW_日期 SCRW_MTM,isnull(SCRW_计划数量,0) SCRW_JHSL,SCRW_累计车次 SCRW_LJCC,'+
//       'isnull(SCRW_累计数量,0)SCRW_LJSL',
//      'cpShengChanRWView with(Nolock)',Wr)+

     CombinQuerySql('row_number()over(order by GCKH_工程名称)rownumber,'+
       'GCKH_ID,GCKH_工程名称 GCKH_MC,GCKH_施工单位 GCKH_SGDW',
      'cpShengChanRWView with(Nolock)',Wr,
      'Group by GCKH_ID,GCKH_工程名称,GCKH_施工单位')+
      ' )SCRW',
     Format('rownumber>%d',[Top*(PageNo - 1)]));
   PSCon_Query.Open;

   Result := GetAppRWDataToJson(
     DB.QueryValue('count(*) rows','cpShengChanRWView with(nolock)',Wr),
     AppDataToJson(FDQuery1,FDQuery1.RecordCount),
     AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.ShengChanRenWu_TZ(FromTM,ToTM,Search,
  SCRW_ID: string;JZ,ISSH,PageNo:Integer):String;
  var
   Wr:string;
begin
 try
   Wr :=Format('FTYS.SCRW_ID=''%s''',[SCRW_ID]);
   if (Search <> '') then
      begin
			Search := Format('FTYS_编号 like ''%%%s%%'' or FTYS_车辆编号 like ''%%%s%%'' '+
      'or FTYS_车辆牌号 like ''%%%s%%''',[Search,Search,Search]);
			Wr := CombinAndSql ( Wr, Search);
     end;
    if JZ>0 then
     Wr := CombinAndSql( Wr, Format('FTYS_生产机组=%d',[JZ]));

   if (ISSH >0) and (FromTM<>'') then
      Wr := CombinAndSql(Wr,Format('FTYS_回单审核>=2 and '+
      'FTYS_审核时间 >=''%s'' and FTYS_审核时间<=''%s''',[FromTM,ToTM]))
    else
    begin
     if (FromTM<>'') then
      Wr := CombinAndSql(Wr,Format('FTYS_时间 >=''%s'' and FTYS_时间<=''%s''',[FromTM,ToTM]));
    end;

   Total_Query.SQL.Clear;
   Total_Query.SQL.Text :=  CombinQuerySql(
     'sum(isnull(FTYS_商砼数量2,0)) as FTYS_YSSL,'+
     'sum(isnull(FTYS_泵送数量2,0)) FTYS_BSSL,Count(*) 累计车次',
     'cpfatongysView FTYS with(Nolock) '+
     'left join cpGongChengKH GCKH with(Nolock) on FTYS.GCKH_ID=GCKH.GCKH_ID '+
     'left join CpshengChanRW SCRW with(Nolock) on FTYS.SCRW_ID=SCRW.SCRW_ID',
     'FTYS_签发审核=1 and FTYS_Del=0 and '+Wr);
   Total_Query.Open;


   PSCon_Query.SQL.Clear;
   PSCon_Query.SQL.Text :=CombinQuerySql('top '+ intToStr(Top) +' * ',
     '('+
       CombinQuerySql('row_number()over(order by FTYS_ISN)rownumber,'+
       'GCKH_ID,FTYS_FZGS,FTYS_编号 FTYS_BH,'+
      'FTYS_时间 FTYS_MTM,FTYS_运输内容名称 FTYS_TYPE,FTYS_车辆编号 FTYS_CLBH,'+
      'FTYS_车辆牌号 FTYS_CPH,isnull(FTYS_商砼数量2,0) FTYS_YSSL,FTYS_生产机组 FTYS_SCJZ,'+
      'isnull(FTYS_累计数量,0) FTYS_LJSL,isnull(FTYS_累计车次,0) FTYS_LJCC',
      'cpfatongysView FTYS with(Nolock) ',
      'FTYS_签发审核=1 and FTYS_Del=0 and '+Wr)+
      ' )FTYS',
     Format('rownumber>%d',[Top*(PageNo - 1)]));

   PSCon_Query.Open;

  Result := GetAppDataToJson(DB.QueryValue('count(*) rows',
    'cpfatongysView FTYS with(Nolock)',Wr),
    AppDataToJson(Total_Query,Total_Query.RecordCount),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

procedure TPSConDataModule.UpdateGKJL(Sqlstr: string);
begin
  try
     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Add('execute PRO_CiGongKSC :Sql');
     PSCon_Query.ParamByName('Sql').Value :=Sqlstr;
     PSCon_Query.Open;

     if (PSCon_Query.recordCount>0) and
        (PSCon_Query.FieldByName('YLMC').AsString<>'') then
     begin
      PSCon_Query.First;
      while not PSCon_Query.Eof do
      begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#ToTalGKJL',
        'GKJL_PNO,GKJL_MTM,GKJL_PL,unit'+
        ',YLMC,设定值,计量值,误差值,误差率',
        Format('%d,''%s'',''%s'',%d,''%s'',%.4f,%.4f,%.4f,%.4f',[

         PSCon_Query.FieldByName('GKJL_PNO').AsInteger
        ,PSCon_Query.FieldByName('GKJL_MTM').AsString
        ,PSCon_Query.FieldByName('GKJL_PL').AsString
        ,PSCon_Query.FieldByName('unit').AsInteger

        ,PSCon_Query.FieldByName('YLMC').AsString
        ,PSCon_Query.FieldByName('设定值').AsFloat
        ,PSCon_Query.FieldByName('计量值').AsFloat
        ,PSCon_Query.FieldByName('误差值').AsFloat
        ,PSCon_Query.FieldByName('误差率').AsFloat])));
       PSCon_Query.Next;
      end;
     end;

 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

procedure TPSConDataModule.UpdateGKJLSum(Sqlstr: string);
begin
  try
     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Add('execute PRO_CiGongKSC :Sql');
     PSCon_Query.ParamByName('Sql').Value :=Sqlstr;
     PSCon_Query.Open;

     if (PSCon_Query.recordCount>0) and
        (PSCon_Query.FieldByName('YLMC').AsString<>'') then
     begin
      PSCon_Query.First;
      while not PSCon_Query.Eof do
      begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#GKJLSum',
        '工程名称,施工单位,YLMC,设定值,计量值,误差值,误差率',
        Format('''%s'',''%s'',''%s'',%.4f,%.4f,%.4f,%.4f',[
        PSCon_Query.FieldByName('工程名称').AsString
        ,PSCon_Query.FieldByName('施工单位').AsString
        ,PSCon_Query.FieldByName('YLMC').AsString
        ,PSCon_Query.FieldByName('设定值').AsFloat
        ,PSCon_Query.FieldByName('计量值').AsFloat
        ,PSCon_Query.FieldByName('误差值').AsFloat
        ,PSCon_Query.FieldByName('误差率').AsFloat])));
       PSCon_Query.Next;
      end;
     end;

 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

procedure TPSConDataModule.UpdateToTalGKJLSum(Sqlstr: string);
begin
  try
     PSCon_Query.SQL.Clear;
     PSCon_Query.SQL.Add('execute PRO_CiGongKSC :Sql');
     PSCon_Query.ParamByName('Sql').Value :=Sqlstr;
     PSCon_Query.Open;

     if (PSCon_Query.recordCount>0) and
        (PSCon_Query.FieldByName('YLMC').AsString<>'') then
     begin
      PSCon_Query.First;
      while not PSCon_Query.Eof do
      begin
        PSCon_Connection.ExecSQL(
        CombinInsertSql('#ToTalGKJLSum',
        'YLMC,设定值,计量值,误差值,误差率',
        Format('''%s'',%.4f,%.4f,%.4f,%.4f',[
         PSCon_Query.FieldByName('YLMC').AsString
        ,PSCon_Query.FieldByName('设定值').AsFloat
        ,PSCon_Query.FieldByName('计量值').AsFloat
        ,PSCon_Query.FieldByName('误差值').AsFloat
        ,PSCon_Query.FieldByName('误差率').AsFloat])));
       PSCon_Query.Next;
      end;
     end;

 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.YuanLiaoYSRB(Search, FromTM, ToTM, UserFID: string;
  PageNo: Integer):String;
  var
   Wr:string;
begin
 try
  if FromTM = ToTM then
    ToTM :=DateTimeToStr(GetStrTodateTime(ToTM)+1/24);

    Wr := Format('YLYS_时间 between ''%s'' and ''%s'' ',[FromTM,ToTM]);

  if (UserFID <> '{11111111-1111-1111-1111-111111111111}' ) then
			Wr := CombinAndSql ( Wr, Format('YLYS_FZGS=''%s''',[UserFID]));

		if (Search <>'') then
    begin
			Search := Format( 'YLYS_编号 like  ''%%%s%%''  or YLYS_运输车辆 like ''%%%s%%'' '+
      'or YLPZ_名称 like ''%%%s%%'' or YLGS_名称 like ''%%%s%%''',
       [Search,Search,Search,Search]);
			Wr := CombinAndSql(Wr,Search);
    end;

   PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +' * ',
    '('+
      CombinQuerySql('row_number()over(order by YLPZ_ID)rownumber,'+
      'YLPZ_ID,YLGS_名称 YLGS_MC,YLYS_FZGS,sum(YLYS_毛重) YLYS_MZ,'+
      'sum(YLYS_皮重) YLYS_PZ,sum(YLYS_验收数量) YLYS_YSSL,YLPZ_名称 YLYS_YLMC',
      'CmYuanLiaoYSView with(Nolock)',Wr,
      'group by YLPZ_ID,YLYS_FZGS,YLPZ_名称,YLGS_名称')+
      ' )YLYS',
     Format('rownumber>%d',[Top*(PageNo - 1)]));
   PSCon_Query.Open;

     Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
   '('+
    CombinQuerySql('count(*) rows',
     'CmYuanLiaoYSView with(nolock)',Wr,
    'group by YLPZ_ID,YLYS_FZGS,YLPZ_名称,YLGS_名称')+
    ')A' ),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount));
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

function TPSConDataModule.YuanLiaoYSTZ(Search, FromTM, ToTM, UserFID,YLYS_YLPZ,YLYS_GGBH: string;
  PageNo,YLPZ_LX: Integer):String;
  var
   Wr:string;
begin

 try
  if FromTM = ToTM then
    ToTM :=FromTM;

    Wr :='isnull(YLYS_DEL,0)=0 and (YLYS_ADD=1) or (YLYS_ADD=0 and YLYS_称验状态=3) and YLYS_审核状态<2';

    Wr := CombinAndSql (Wr,Format('YLYS_时间 between ''%s'' and ''%s''',[FromTM,ToTM]));

  if (UserFID <> '{11111111-1111-1111-1111-111111111111}' ) then
			Wr := CombinAndSql ( Wr, Format('YLYS_FZGS=''%s''',[UserFID]));

		if (Search <>'') then
    begin
			Search := Format( 'YLYS_编号 like  ''%%%s%%''  or YLYS_运输车辆 like ''%%%s%%'' '+
      'or YLPZ_品种名称 like ''%%%s%%'' or YLGS_名称 like ''%%%s%%''',
       [Search,Search,Search,Search]);
			Wr := CombinAndSql(Wr,Search);
    end;

   if not YLYS_YLPZ.IsEmpty then
     Wr := CombinAndSql ( Wr, Format('YLPZ_品种名称=''%s''',[YLYS_YLPZ]));
   if not YLYS_GGBH.IsEmpty then
    Wr := CombinAndSql ( Wr, Format('YLPZ_规格等级=''%s''',[YLYS_GGBH]));

  if YLPZ_LX>0 then
     Wr := CombinAndSql(Wr,Format('YLPZ_原料类型=%d',[YLPZ_LX]));

   Total_Query.SQL.Clear;
   Total_Query.SQL.Text :=CombinQuerySql('sum(isnull(YLYS_验收数量,0))YLYS_YSSL,Count(*)YLYS_LJCC',
   'CmYuanLiaoYSView with(Nolock)',Wr);
   Total_Query.Open();

   PSCon_Query.SQL.Clear;
    PSCon_Query.SQL.Text := CombinQuerySql('top '+ intToStr(Top) +' * ',
    '('+
      CombinQuerySql('row_number()over(order by YLYS_时间)rownumber,'+
      'YLPZ_ID,YLYS_FZGS,YLYS_编号 YLYS_BH ,YLYS_运输车辆 YLYS_YSCL,'+
      'YLYS_毛称时间 YLYS_MC_MTM,YLYS_皮称时间 YLYS_PC_MTM,'+
			'YLYS_毛重 YLYS_MZ,YLYS_皮重 YLYS_PZ,YLYS_时间,YLYS_验收数量 YLYS_YSSL,'+
      'YLGS_名称 YLGS_MC,YLPZ_名称 YLYS_YLMC',
      'CmYuanLiaoYSView with(Nolock)',Wr)+
      ' )YLYS',
     Format('rownumber>%d',[Top*(PageNo - 1)]));
   PSCon_Query.Open;

   Result := GetAppDataToJson( DB.QueryValue('count(*) rows',
   'CmYuanLiaoYSView with(Nolock)',Wr ),
    AppDataToJson(Total_Query,Total_Query.RecordCount),
    AppDataToJson(PSCon_Query,PSCon_Query.RecordCount))
 except
    on E: Exception do
    begin
      Log('!'+E.ToString);
      raise;
    end;
 end;
end;

end.
